I don’t normally have a need to do dynamic sql; however, I found myself at a client site and we were doing a dynamic query where we wanted to capture the return value. They dynamic nature of the query was giving me grief with the Exec command and this led me the use sp_executesql. This may be old hat for some, but it was new to me. The syntax is fairly straight forward.
DECLARE @RetrunVariable INT
DECLARE @SQL NVARCHAR (4000)
DECLARE @ParamDefinition nvarchar(500)
SET @SQL = N’Select @RetrunVariable=Column FROM Table where column = x’
Set @ParamDefinition =N’@TimeIDOut INT OUTPUT’
Exec sp_executesql @sql, @ParamDefinition, @TimeIDOut = @RetrunVariable OUTPUT;
What took me a moment to get was I declare a variable in the @ParamDefinition to retrieve the variable I am passing back and the variable must be assigned first in the exec line. For example, we had @RetrunVariable = @TimeIDOut and that did not work. Only when we switched the variables did we get our required result.
Leave a Reply
You must belogged in to post a comment.