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

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