Dynamic SQL: Why does following not work? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL: Why does following not work?

Author

Topic

landau66
Yak Posting Veteran

61 Posts

Posted - 2014-01-21 : 15:00:02

Hi everyone,

I am working on a dynamic SQL Statement. It is very simple and I think i didnt commit a mistake. Why is it not working:

exec sp_executesql N'select * from @tableName', N'@tableName nvarchar(50)', @tableName= 'dax2'

This Statement works very well: select * from dax2

When I use the Version with 'sp_executesql' then i get following error:
Msg 1087, Level 16, State 1, Line 1
Must declare the table variable "@tableName".

Do you see the mistake? I wrote it from new several times - but still the same result :-(

Many thanks!
landau

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-21 : 15:17:47

declare @tablename sysname, @sql varchar(4000)
set @tablename = 'dax2'
set @sql = 'select * from ' + @tablename
exec sp_executesql @sql

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 06:58:46

one question here is why is your table changing? Most often this is a case of not designing system properly resulting in creation of several unwanted runtime table objects.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

landau66
Yak Posting Veteran

61 Posts

Posted - 2014-01-22 : 10:22:54

thank you very much for the answers!

I tried the way Tara suggested and it worked. I also learned about the other way with sp_executesql and its parameters. The following is working:

exec sp_executesql N'select * from dax2 where [date] = @datum', N'@datum date', @datum = '2000-01-03'

The following does not work:

exec sp_executesql N'select * from @tableName', N'@tableName sysname', @tableName = 'INDIZES.dbo.dax2'

What is the reason for that?

Thank u very much
Landau

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources