Access Variable Outside Dynamic SQL

Question

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'.

Answer 1

_{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

Answer 2

Paul White's answer is correct, but I'd also add that in the original post, the @sql is not defined correctly. The correct script should be

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


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

i.e. notice inside @sql, there is no 'declare @value1 varchar(100)'. If you keep it there, you will have this error

Msg 134, Level 15, State 1, Line 7

The variable name '@value1' has already been declared. Variable names must be unique within a query batch or stored procedure.