Assign output of dynamic SQL to a variable (without stored proc)

Question

I have the code below, where I want to get the id of a row via dynamic SQL.

Please note that variables @posselect and @catselect are filled, but I left that out for overview.

declare @SQL nvarchar(1000)
declare @posselect nvarchar(50)
declare @catselect nvarchar(100)

declare @bannerid int

SET @SQL = 'SELECT TOP 1 @id = id FROM banners
            WHERE publishdate < GETDATE() 
            AND depublishdate > GETDATE()' + @posselect + @catselect + 
           'ORDER BY [views] ASC'

EXEC sp_executesql @SQL, @bannerid = @id output  'on this line I get the error

print '@bannerid:'+STR(@bannerid)

I get the error:

Must declare the scalar variable "@id".

Having difficulties posting code, dont know the tag usage. Replaced @ with [at] Fixed it like this: declare [at]ParmDefinition nvarchar(250) set [at]ParmDefinition = '[at]bannerid int out' set [at]SQL='SELECT TOP 1 [at]bannerid=id FROM banners WHERE publishdate<GETDATE() AND depublishdate>GETDATE()' + [at]posselect + [at]catselect + ' ORDER BY [views] ASC' EXEC sp_executesql [at]SQL,[at]ParmDefinition,[at]bannerid out thanks! — Adam
– Adam, Commented Aug 5, 2011 at 21:41

Taryn · Accepted Answer · 2011-08-05 17:42:01Z

2

Your code doesn't show that you are setting your @id field

declare @SQL nvarchar(1000)
declare @posselect nvarchar(50)
declare @catselect nvarchar(100)

declare @id int
declare @bannerid int

set @SQL='SELECT TOP 1 @id=id FROM banners
WHERE publishdate<GETDATE() AND depublishdate>GETDATE()' + @posselect + @catselect + '
ORDER BY [views] asc'

EXEC sp_executesql @SQL,@bannerid=@id output  'on this line I get the error

print '@bannerid:'+STR(@bannerid)

answered Aug 5, 2011 at 17:42

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abe Miessler · Accepted Answer · 2011-08-05 17:40:53Z

2

I might be missing something but I don't see where you are declaring @id (as the error message suggests).

Try changing it to this:

declare @SQL nvarchar(1000) declare @posselect nvarchar(50) declare @catselect nvarchar(100)

declare @bannerid int
declare @id int

set @SQL='SELECT TOP 1 @id=id FROM banners WHERE publishdateGETDATE()' + @posselect + @catselect + ' ORDER BY [views] asc'

EXEC sp_executesql @SQL,@bannerid=@id output 'on this line I get the error

print '@bannerid:'+STR(@bannerid)

answered Aug 5, 2011 at 17:40

Abe Miessler

85.6k104 gold badges323 silver badges496 bronze badges

Comments

Sam DeHaan · Accepted Answer · 2011-08-05 17:57:59Z

1

Try

DECLARE @params NVARCHAR(128) = N'@id int output';

EXEC sp_executesql @SQL, @params, @id=@bannerid output

I believe that's the correct syntax.

edited Aug 5, 2011 at 17:57

answered Aug 5, 2011 at 17:40

Sam DeHaan

10.4k2 gold badges42 silver badges48 bronze badges

Collectives™ on Stack Overflow

Assign output of dynamic SQL to a variable (without stored proc)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related