Setting a variable from dynamic sql

-- setting a variable from dynamic sql
Author Nigel Rivett

 

declare @i int

exec sp_executesql N'select @i = 999', N'@i int output', @i output

select @i

 

 

-- setting output parameter from dynamic stored procedure call

 

declare @OutputParameter varchar(100) ,

        @error int ,

        @SPName varchar(128) ,

        @SPCall nvarchar(128) ,

        @rc int

select @SPCall = 'exec ' + @SPName + ' @OutputParameter output'

exec @rc = sp_executesql @SPCall, N'@OutputParameter varchar(100) output', @OutputParameter output

select @Error = @@error

 

reference: http://www.nigelrivett.net/SQLTsql/sp_executeSQL.html