Using SQl Server 2000 I have a stored procedure that joins 2 tables and then returns the data. I want this sp to be able to do this for whatever table name I pass into it, otherwise I'll have the exact same code with the exception of the table name 20 or so times in a giant if statement. Basically, how do I use a variable to point to a table, or is that allowed? Thanks.
A:
Try building the SELECT as a string and then calling EXEC and passing the string.
e.g.
declare @sql varchar(500)
set @sql = 'select whatever from ' + @tableName
exec @sql
froadie
2010-08-25 19:10:53
Valid, but risky: http://xkcd.com/327/
OMG Ponies
2010-08-25 19:28:00
+4
A:
You need dynamic SQL, start here The Curse and Blessings of Dynamic SQL to learn how to do it correctly so that nobody drops your tables or does anything else possible with SQL Injection
SQLMenace
2010-08-25 19:11:38
+1
A:
One proc to do anything is usually a bad bad idea. 20 possible tables I might need to go to depending on the circumstances almost always indicates a database design that is bad. Read the article Denis posted on the curses and blessings of dynamic SQL.
HLGEM
2010-08-25 19:50:26
There are certain columns in every table that are consistent, and that is what I am pulling. Would it be better to have 20 if statements?
Shawn
2010-08-25 20:13:08
If I had five columns that were the same in 20 tables, I would pull them out to a separate parent table. That's what I mean about your database design being bad.
HLGEM
2010-08-25 20:27:54
Possibly, but it wouldn't work with how I'm displaying the data and updating it.
Shawn
2010-08-26 12:06:02