Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

EXEC sql string that has table variable Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 7:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:05 AM
Points: 34, Visits: 90

Hi,

Why the following code gives error? I have @temp table variable already declared, @p_SampleRecords variable already declared and assigned a value.


Declare @strSql varchar(max)
set @strSql = 'select top ' + cast(@p_SampleRecords as varchar) + ' * from @temp order by TStamp desc'
Exec (@strSql)

Thanks & Regards,
Post #1405467
Posted Thursday, January 10, 2013 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 13,082, Visits: 12,546
gsd1 (1/10/2013)

Hi,

Why the following code gives error? I have @temp table variable already declared, @p_SampleRecords variable already declared and assigned a value.


Declare @strSql varchar(max)
set @strSql = 'select top ' + cast(@p_SampleRecords as varchar) + ' * from @temp order by TStamp desc'
Exec (@strSql)

Thanks & Regards,


Your dynamic sql runs in a new batch. You think you have @p_SampleRecords variable already declared and assigned a value but that is not in the same batch. From what you posted I don't think you need dynamic sql at all.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1405470
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse