November 20, 2012 at 9:04 pm
Hi All ,
I need a dynamic sql query (db is sql server 2000) which assigns a value = 1 to a user variable if a column is null in a table as listed below:
Declare @i INT
Select @i = 1 From Production.Product Where Color Is Null
Print @I
I need to rewrite the sql statement using the dynamic sql. Can I use sp_executesql for it?
Thank You.
November 23, 2012 at 12:06 pm
I'm guessing that you can, as long as you include the keyword 'OUTPUT' in all the appropriate places. This snipped returned 123 as a result of the final select statement. Strictly speaking however, @I 's scope is limitted to the scope defined by sp_executesql, but the OUTPUT clause lets you pass results back to the scope that executed the sp_executesql.
DECLARE @test1 NVARCHAR(500)
DECLARE @params NVARCHAR(500)
DECLARE @I_OUT INT
SET @test1 = 'Select @I = 123'
SET @params = '@I INT OUTPUT'
SELECT @test1
EXEC sp_executesql @test1, @params, @I_OUT OUTPUT
SELECT @I_OUT
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply