| Author |
Topic |
|
BartMan
Starting Member
22 Posts |
 Posted - 2003-09-12 : 12:21:54
|
| Greetings,I am trying to use a table name as a param within a procedure to call from an sql statement, but I can't seem to get it to work.-- A test precedure exampleCREATE PROCEDURE sp_TestTable@TableNameParam nvarchar(40)ASBEGIN-- Do some type of sql operation on the table.-- A simple select for an example.SELECT * FROM @TableNameParamENDThe error I receive is "Must declare @TableNameParam".Is this something that can be done in sql within procedures?Thanks in advance for any suggestions. |
|
|
dsdeming
479 Posts |
Posted - 2003-09-12 : 12:26:55
|
| You hace to use dynamic SQL for that. See EXECUTE and sp_executesql in BOL.Dennis |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-12 : 12:30:46
|
| Typically, when a single stored procedure or action needs to have a table or database object specified as a parameter, it indicates there is probably a design flaw in your database. I.e., where you have 1 set of tables per customer or per user or something like that.- Jeff |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-09-12 : 12:50:47
|
There's something about 1500 posts that brings out the best bitterness in people. "I'm not talking the bitterness of juicy lime in a Corona. I'm talking about the bitter smell of a spent round in the skull of the unrighteous." (paraphrased quote from setbasedisthetruepath)Congrats Jeff on joining us over here.  Jay White{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-12 : 12:59:58
|
| I must have been seduced by the dark side ...- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-12 : 13:05:39
|
| Reality sucks....but it's all we have...I bet Jeff's guess about the set of tables per user aint to far off the mark...They could make a movie about this site though...Episode 24...the return of crespo...Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
BartMan
Starting Member
22 Posts |
Posted - 2003-09-12 : 13:41:57
|
quote:
Originally posted by dsdeming
You hace to use dynamic SQL for that. See EXECUTE and sp_executesql in BOL.Dennis
Thanks Dennis,That is exactly what I needed! |
|
|
|
BartMan
Starting Member
22 Posts |
Posted - 2003-09-12 : 13:49:00
|
quote:
Originally posted by jsmith8858
Typically, when a single stored procedure or action needs to have a table or database object specified as a parameter, it indicates there is probably a design flaw in your database. I.e., where you have 1 set of tables per customer or per user or something like that.- Jeff
Hello Jeff,The database I am working with has about 5 tables with 1 common date time field in each. This date time field is used for maintenance on the records, thus the reason for a procedure. The tables themselves have different fields (except for the datetime stamp).The procedure is then used to delete records from a certain date time frame.At any rate I thought I would explain the design a little bit.It is always fun maintaining a database that someone else designed. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-12 : 13:53:56
|
| That's a good point, actually. "System maintence" stored procedures, run ONLY by the DBA, can definitely make sense to take database objects as parameters and use dynamic SQL to perform some sort of maintenance in your database. Those are OK. - Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-12 : 14:34:20
|
| Then here is what dynamic sql looks like:DECLARE @SQL VARCHAR(7000)SELECT @SQL = 'SELECT * FROM ' + @TableName + ' WHERE Column1 = 0'EXEC (@SQL)Tara |
|
|
|
BartMan
Starting Member
22 Posts |
Posted - 2003-09-12 : 16:13:32
|
quote:
Originally posted by tduggan
Then here is what dynamic sql looks like:DECLARE @SQL VARCHAR(7000)SELECT @SQL = 'SELECT * FROM ' + @TableName + ' WHERE Column1 = 0'EXEC (@SQL)Tara
Thanks Tara,Dynamic sql is very cool, and your example was very helpful. |
|
|
|
|
A Procedure with a table name as a param?