How can I pass a variable for use as table name in a procedure?

Question

Post reply

How can I pass a variable for use as table name in a procedure?

Hakman

Newbie

Points: 2
More actions
March 9, 2006 at 9:08 am

#171362

I would like to write a procedure that selects all rows from a table that is passed in as a variable.
For example if I have three tables Group_A_Items, Group_B_Items, and Group_C_Items how would I write a procedure 'list_table' so that
exec list_table A
would return all the rows in table Group_A_Items?
TIA for any help
Duane

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Why would you want to do this?

It can be done, but you need to understand the pro's and cons to do this, and if your willing to sacrafice a little.

Do a search on this site for dynamic sql. There are tons of posts and examples

look at Books onLine sp_executeSql

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

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

You can pass in the table name only as a text string (varchar/char argument). But you cannot use that table name in a query directly. Your sproc would either have to test for the text string value, and run a separate query for each possible value, or create a sql statement and run it use execute statement in the proc (this is referred to as dynamic sql).

Option 1:

IF (@argTable = 'Group_A_Items')
  SELECT * from Group_A_Items

IF (@argTable = 'Group_B_Items')
  SELECT * from Group_B_Items

etc.

Option 2:

DECLARE @sql varchar(1000)
SET @sql = 'SELECT * FROM ' + @argTable
EXEC @sql

Hope this helps

Mark

Hakman Newbie Points: 2 More actions · Answer 3

Thank you Ray and Mark for your ideas. The query I'm actually using is quite complex but you both have given me some ideas on how to structure my procedure.

Best regards and thanks again,

Duane