Sql-server – Access Variable Outside Dynamic SQL

dynamic-sqlsql serversql-server-2008-r2

What am I missing here? If I run a print @sql statement and run that statement directly in SSMS I get the value that I am after. However, if I run the dynamic sql and try to access Print @value1 outside the dynamic sql no value is returned.

How can I access the value of @value1 from outside my dynamic sql? In my syntax below, it always returns nothing, as if the value does not exist!

Declare @Name varchar(100), @value1 varchar(100), @sql nvarchar(max)
Set @Name = 'Jalen'
set @sql = 'Declare @value1 varchar(100) Select @value1 = id 
            from openquery(192.168.500.300,''Select id 
             from massmailer where empname in ('''''+@Name+N''''')'')'
Print @value1
EXEC sp_executesql @SQL

I tried to modify my Exec statement to:

EXEC sp_executesql @SQL, @retvalOUT=@value1 OUTPUT;

This gives me an error of:

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Best Answer

Community Wiki answer originally left as question comments

You need:

EXEC sp_executesql
    @sql, 
    N'@value1 varchar(100) OUTPUT',
    @value1=@value1 OUTPUT;

An online demonstration of the technique: http://rextester.com/GDL49591

Related Question