SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Can a Table be passed to sp_executesql?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 01/07/2010 :  09:24:16  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/07/2010 :  09:28:19  Show Profile  Reply with Quote
yup you can. sql should be:-

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

Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 01/07/2010 :  09:43:24  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/07/2010 :  09:44:53  Show Profile  Reply with Quote
sorry didnt get that. where's the variable definition?
Go to Top of Page

shan
Yak Posting Veteran

USA
84 Posts

Posted - 01/07/2010 :  09:45:56  Show Profile  Reply with Quote
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

India
52325 Posts

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

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 01/07/2010 :  09:58:51  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/07/2010 :  10:03:49  Show Profile  Reply with Quote
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 - 01/07/2010 :  10:07:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 01/07/2010 :  10:10:47  Show Profile  Visit webfred's Homepage  Reply with Quote
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

Canada
594 Posts

Posted - 01/07/2010 :  10:22:55  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/07/2010 :  10:36:50  Show Profile  Reply with Quote
you cant pass table as a parameter until 2008. can i ask need for that?
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 01/07/2010 :  10:46:43  Show Profile  Reply with Quote
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

Canada
594 Posts

Posted - 01/07/2010 :  11:00:19  Show Profile  Reply with Quote
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

Canada
594 Posts

Posted - 01/07/2010 :  11:03:55  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/07/2010 :  11:05:03  Show Profile  Reply with Quote
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

Canada
594 Posts

Posted - 01/07/2010 :  11:07:54  Show Profile  Reply with Quote
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.

Edited by - denis_the_thief on 01/07/2010 11:17:48
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/07/2010 :  11:23:10  Show Profile  Reply with Quote
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

India
18 Posts

Posted - 01/08/2010 :  06:04:15  Show Profile  Reply with Quote
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

India
22769 Posts

Posted - 01/08/2010 :  07:46:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000