Access Variable Outside Dynamic SQL 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'.
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