Out Param With OpenQuery

Question

I am trying to output a parameter from openquery, but my syntax produces this error

Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@NameID".
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@NameID".

What in my syntax should be altered in order to have @NameID returned from the query?

Declare @sql nvarchar(4000), @Name nvarchar(100), @Nameid nvarchar(25)
SET @Name = 'Foxtrot'
SET @SQL = 'DECLARE @NameID nvarchar(25) Select @Nameid = id FROM OPENQUERY(BlueBox, ''Select id from employees where id = '+CHAR(39)+CHAR(39)+@Name+CHAR(39)+CHAR(39)+ N' '')'
PRINT @SQL
EXEC sp_executesql @sql, @NameID = @NameID OUTPUT;
Select @NameID

• Laughing Vergil: You need the variable @NameID to receive the output of @NameID. Declare it. (I suspect you have mixed up @Name and @NameID)
• SmallFries BigGuys: I declared @NameID outside my dynamic SQL but my select returns null everytime. If I run the select statement from my openquery directly against the server it returns a value?
• McNets: Can you show the value of @Sql?
• SmallFries BigGuys: @mcNets -> If I print the value of SQL and run that statement directly in SSMS it produces the appropriate result.

Answer 1

Your EXEC line needs to change - you declare the internal variables in the sp_executeSQL command itself. So your overall query becomes:

Declare @sql nvarchar(4000), @Name nvarchar(100), @Nameid nvarchar(25)
SET @Name = 'Foxtrot'
SET @SQL = 'Select @Nameid = id FROM OPENQUERY(BlueBox, ''Select id from employees where id = '+CHAR(39)+CHAR(39)+@Name+CHAR(39)+CHAR(39)+ N' '')'
PRINT @SQL
EXEC sp_executesql @sql, N'@NameID nvarchar(25) OUTPUT', @NameID = @NameID OUTPUT;
Select @NameID

Answer 2

According to sp_executesql documentation you need a @ParamDefinition.

Declare @sql nvarchar(4000)
Declare @ParamDefinition nvarchar(400)
Declare @Name varchar(100)
Declare @NameID nvarchar(25)
SET @SQL = N'Select @Nameid = id FROM OPENQUERY(BlueBox, ''Select id from employees where id = '+CHAR(39)+CHAR(39)+@Name+CHAR(39)+CHAR(39)+ N' '')'
SET @ParamDefinition = N'@Name varchar(100), @NameId nvarchar(25) OUTPUT'
EXEC sp_executesql @SQL, @ParamDefinition, @Name='Foxtrot', @NameID = @NameID OUTPUT;
Select @NameID

You can check it here: http://rextester.com/WTCOZ80449