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 2005 Forums
 Transact-SQL (2005)
 Can a Table be passed to sp_executesql?

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

Go to Top of Page

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 ' + @T
exec sp_executesql @SQL

set @T = 'Table2'
set @SQL = '
select * from ' + @T
exec sp_executesql @SQL

set @T = 'Table3'
set @SQL = '
select * from ' + @T
exec sp_executesql @SQL


Go to Top of Page

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?
Go to Top of Page

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 ' + @T
exec sp_executesql @SQL

set @SQL =''
set @T = 'Table2'
set @SQL = '
select * from ' + @T
exec sp_executesql @SQL

set @SQL =''
set @T = 'Table3'
set @SQL = 'select * from ' + @T
exec sp_executesql @SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 09:54:15
also see
http://msdn.microsoft.com/en-us/library/ms188001.aspx
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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 visakh16

set @SQL = 'select * from ' + @T

As 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
Go to Top of Page

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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-01-07 : 10:22:55
quote:
Originally posted by visakh16

also see
http://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.
Go to Top of Page

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?
Go to Top of Page

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)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM ' + @tablename
Exec ( @SQL)

GO

exec Search @tablename = 'Accounts'
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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??
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-08 : 06:04:15
Try if this piece of info helps you

http://www.tek-tips.com/faqs.cfm?fid=4107



balaganapathy n.

Anything you can imagine is real.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-08 : 07:46:56
quote:
Originally posted by balaganapathy.n

Try if this piece of info helps you

http://www.tek-tips.com/faqs.cfm?fid=4107



balaganapathy n.

Anything you can imagine is real.


Dony rely on undocumented procedures
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -