Author |
Topic |
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-01-07 : 09:24:16
|
Can a Table be passed as a parameter to sp_executesql?I am stuck on something like this:set @SQL = 'select * from @T'exec sp_executesql @SQL, N'@T varchar(50)', @T = 'Accounts'; I get the error: Must declare the table variable "@T". |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 09:28:19
|
yup you can. sql should be:-set @SQL = 'select * from ' + @T |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-01-07 : 09:43:24
|
quote: Originally posted by visakh16 yup you can. sql should be:-set @SQL = 'select * from ' + @T
But then I get into a situation like this:declare @T varchar(50), @Sql nvarchar(4000)set @T = 'Table1'set @SQL = 'select * from ' + @Texec sp_executesql @SQLset @T = 'Table2'set @SQL = 'select * from ' + @Texec sp_executesql @SQLset @T = 'Table3'set @SQL = 'select * from ' + @Texec sp_executesql @SQL |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 09:44:53
|
sorry didnt get that. where's the variable definition? |
|
|
shan
Yak Posting Veteran
84 Posts |
Posted - 2010-01-07 : 09:45:56
|
Are you getting error when you execute for table2?try this...declare @T varchar(50), @Sql nvarchar(4000)set @T = 'Table1'set @SQL = 'select * from ' + @Texec sp_executesql @SQLset @SQL =''set @T = 'Table2'set @SQL = 'select * from ' + @Texec sp_executesql @SQLset @SQL =''set @T = 'Table3'set @SQL = 'select * from ' + @Texec sp_executesql @SQL |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 09:54:15
|
also seehttp://msdn.microsoft.com/en-us/library/ms188001.aspx |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-01-07 : 09:58:51
|
quote: Originally posted by shan Are you getting error when you execute for table2?
In the first example I provided, I am getting an error.In the one you quoted, I am not getting an error. But that is the type of code I am trying to avoid by using a patameter (ie. avoid repeating code). In my actually problem, my SQL is much longer and uses the table reference many times and I wish to call it 4-10 times with different tables each time. I was hoping to just call the sql with sp_executesql each time and just provide the table name as a parameter. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 10:03:49
|
quote: Originally posted by denis_the_thief
quote: Originally posted by shan Are you getting error when you execute for table2?
In the first example I provided, I am getting an error.In the one you quoted, I am not getting an error. But that is the type of code I am trying to avoid by using a patameter (ie. avoid repeating code). In my actually problem, my SQL is much longer and uses the table reference many times and I wish to call it 4-10 times with different tables each time. I was hoping to just call the sql with sp_executesql each time and just provide the table name as a parameter.
you can call sql each time by passing table name as parameter. why you need to repeat it? can you explain that part? also why does your table name vary dynamically? |
|
|
jcampbell
Starting Member
9 Posts |
Posted - 2010-01-07 : 10:07:38
|
you can not pass the table name as a parametr to sp_executesql, you must use the example from visakh16set @SQL = 'select * from ' + @TAs a Side note, you can pass columns names as parameters in the select list (IN 2008 only), however, you can not pass column names in the order by or group by clause using the sp parameters |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-07 : 10:10:47
|
If you really need it to do...Make a stored procedure to handle that. No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-01-07 : 10:22:55
|
quote: Originally posted by visakh16 also seehttp://msdn.microsoft.com/en-us/library/ms188001.aspx
Thank you.I have looked at that reference. It does not provide any example of passing a table as a parameter yet it does not say you can't. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 10:36:50
|
you cant pass table as a parameter until 2008. can i ask need for that? |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-01-07 : 10:46:43
|
Like Fred mentioned...is needed you can use an SP to handle this..like below.,.Create Procedure Search @tablename VarChar(100)ASDeclare @SQL VarChar(1000)Select @SQL = 'SELECT * FROM ' + @tablenameExec ( @SQL)GOexec Search @tablename = 'Accounts' |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-01-07 : 11:00:19
|
quote: Originally posted by visakh16 you cant pass table as a parameter until 2008. can i ask need for that?
My take is: to avoid using dynamic SQL. Dynamic SQL is all fun and games until you have to maintain someone else's code. Plus there is no error-checking at compile time.Which I guess begs the question why am I trying to use it in the first place. |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-01-07 : 11:03:55
|
quote: Originally posted by jcampbell you can not pass the table name as a parametr to sp_executesql
That is what I was afraid of. Thankyou for confirming it though. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 11:05:03
|
quote: Originally posted by denis_the_thief
quote: Originally posted by visakh16 you cant pass table as a parameter until 2008. can i ask need for that?
My take is: to avoid using dynamic SQL. Dynamic SQL is all fun and games until you have to maintain someone else's code. Plus there is no error-checking at compile time.Which I guess begs the question why am I trying to use it in the first place.
can you explain the need for this? i cant understand why your table name varies and need to use dynamic sql?? |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-01-07 : 11:07:54
|
quote: ... also why does your table name vary dynamically?
I have an insert statement from sys.modules and sys.objects. But I want to call this insert statment for the sys tables from several different Databases. I may have to go with the separate Stored Proc idea that was mentioned.I guess I could have explained all that and posted the long SQL at the beginning. But I prefer to start posts with just what I believe is the main point. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 11:23:10
|
quote: Originally posted by denis_the_thief
quote: ... also why does your table name vary dynamically?
I have an insert statement from sys.modules and sys.objects. But I want to call this insert statment for the sys tables from several different Databases. I may have to go with the separate Stored Proc idea that was mentioned.I guess I could have explained all that and posted the long SQL at the beginning. But I prefer to start posts with just what I believe is the main point.
but for executing sql in all dbs you can make use undocumented stored procedure sp_Msforeachdb |
|
|
balaganapathy.n
Starting Member
18 Posts |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|