Pass Table Name to Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Pass Table Name to Procedure

Author

Topic

dtucker
Starting Member

4 Posts

Posted - 2006-08-16 : 14:04:41

Does anybody know how to pass a table name to a procedure? Take the following example:

CREATE PROCEDURE spShowTable @tableName varchar(30) AS
SELECT * FROM @tableName

EXEC spShowTable Users

I would expect that EXEC statement to display the Users table. (I am just using this example because it is simple. What I am actually trying to do is more useful.) I get the following error when I try to execute that procedure:

Msg 1087, Level 15, State 2, Procedure spShowTable, Line 2
Must declare the table variable "@tableName".

Does anybody know how to make this work?

Thank you,

David

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 14:28:12

This is a very, very, very (repeated a zillion times) bad thing to do. Why do you need to dynamically select from a table. There is absolutely no point to using a stored procedure for this. You might as well use inline SQL in your application. Please explain why you want to do this.

Tara Kizer

dtucker
Starting Member

4 Posts

Posted - 2006-08-16 : 14:43:59

I am working on a pagination system. Instead of writing 15 nearly identical procedures (only differing in the table name) to return data in pages, I want to write one procedure, that will take a page number, page size, and table name.

I do not have to do it this way, but I like to encapsulate code whenever possible.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 14:46:29

This is highly not recommended for performance and security reasons. You really should write the 15 objects. There is a way to do what you want, but it will perform much slower than the 15 objects would, plus it will be harder to maintain. I can urge you enough not to do this. But if you really must, then you can check out the dynamic SQL article here. I will not post the link to it as you shouldn't do it this way. So if you really must, then you should be able to find it here with some searching.

Tara Kizer

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 15:02:18

If u know the 15 table names, u can copy-paste the same procedure 15 times, do the table name changes and run once. U r done.

Srinika

dtucker
Starting Member

4 Posts

Posted - 2006-08-16 : 15:15:44

Thank you both very much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-17 : 10:16:02

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail