Stored procedure, pass table name as a parameter

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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