EXEC sql string that has table variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » EXEC sql string that has table variable

EXEC sql string that has table variable

Rate Topic

Display Mode

Topic Options

Author

Message

gsd1

Posted Thursday, January 10, 2013 7:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:02 AM
Points: 30, Visits: 82

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

Sean Lange

Posted Thursday, January 10, 2013 7:55 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 8,547, Visits: 8,204

gsd1 (1/10/2013)

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

Post #1405470

« Prev Topic | Next Topic »

Permissions