CODEWITHSUNDEEP
sqlsql-server-2005
bigtv
bigtv

Reputation: 2711

Stored procedure, pass table name as a parameter

I have about half a dozen generic, but fairly complex stored procedures and functions that I would like to use in a more generic fashion.

Ideally I'd like to be able to pass the table name as a parameter to the procedure, as currently it is hard coded.

The research I have done suggests I need to convert all existing SQL within my procedures to use dynamic SQL in order to splice in the dynamic table name from the parameter, however I was wondering if there is a easier way by referencing the table in another way?

For example:

SELECT * FROM @MyTable WHERE...

If so, how do I set the @MyTable variable from the table name?

I am using SQL Server 2005.

Upvotes: 2

Views: 7160

Answers (5)

user4523002
user4523002

Reputation: 1

ALTER procedure [dbo].[test](@table_name varchar(max))
 AS
 BEGIN
  declare @tablename varchar(max)=@table_name;
  declare @statement varchar(max);
  set @statement = 'Select * from ' + @tablename;
  execute (@statement);
 END

Upvotes: 0

Chris Diver
Chris Diver

Reputation: 19802

You can use dynamic Sql, but check that the object exists first unless you can 100% trust the source of that parameter. It's likely that there will be a performance hit as SQL server won't be able to re-use the same execution plan for different parameters.

IF OBJECT_ID(@tablename, N'U') IS NOT NULL
BEGIN 
    --dynamic sql
END

Upvotes: 1

Fosco
Fosco

Reputation: 38516

EXEC(N'SELECT * from ' + @MyTable + N' WHERE ...     ')

Upvotes: 1

Tom H
Tom H

Reputation: 47444

Dynamic SQL is the only way to do this, but I'd reconsider the architecture of your application if it requires this. SQL isn't very good at "generalized" code. It works best when it's designed and coded to do individual tasks.

Selecting from TableA is not the same as selecting from TableB, even if the select statements look the same. There may be different indexes, different table sizes, data distribution, etc.

You could generate your individual stored procedures, which is a common approach. Have a code generator that creates the various select stored procedures for the tables that you need. Each table would have its own SP(s), which you could then link into your application.

I've written these kinds of generators in T-SQL, but you could easily do it with most programming languages. It's pretty basic stuff.

Just to add one more thing since Scott E brought up ORMs... you should also be able to use these stored procedures with most sophisticated ORMs.

Upvotes: 3

ScottE
ScottE

Reputation: 21630

You'd have to use dynamic sql. But don't do that! You're better off using an ORM.

Upvotes: 1

Related Questions