sp_executesql - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

sp_executesql

Author

Topic

durairaj.arumugam
Starting Member

6 Posts

Posted - 2008-06-26 : 07:14:17

While running this query I getting error as
"Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@p_tab'."

Query:
------
declare @p_tab nvarchar(50)
declare @t datetime
declare @sql nvarchar(500)
declare @pharm_def nvarchar(100)
declare @tab varchar(50)
set @tab='t1'
set @t = getdate()
set @sql='select * from @p_tab'
set @pharm_def = N'@p_tab varchar(50)'
exec sp_executesql @sql, @pharm_def, @p_tab=@tab;
print datediff(second,@t,current_timestamp)

pls let me know what is the problem in this query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 07:47:06

you dont require sp_executesql here
you just need

set @sql='select * from '+@tab
exec(@sql)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 09:06:44

quote:
Originally posted by durairaj.arumugam

pls let me know what is the problem in this query.

You can't use table name or column name as a parameter.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 09:52:27

quote:
Originally posted by durairaj.arumugam

While running this query I getting error as
"Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@p_tab'."

Query:
------
declare @p_tab nvarchar(50)
declare @t datetime
declare @sql nvarchar(500)
declare @pharm_def nvarchar(100)
declare @tab varchar(50)
set @tab='t1'
set @t = getdate()
set @sql='select * from @p_tab'
set @pharm_def = N'@p_tab varchar(50)'
exec sp_executesql @sql, @pharm_def, @p_tab=@tab;
print datediff(second,@t,current_timestamp)

pls let me know what is the problem in this query.

Do you really need to pass table name as parameter?
Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources