0

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
  • You need the variable @NameID to receive the output of @NameID. Declare it. (I suspect you have mixed up @Name and @NameID) – Laughing Vergil Dec 19 '16 at 20:26
  • 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? – SmallFries BigGuys Dec 19 '16 at 20:50
  • Can you show the value of @Sql? – McNets Dec 19 '16 at 20:58
  • @mcNets -> If I print the value of SQL and run that statement directly in SSMS it produces the appropriate result. – SmallFries BigGuys Dec 19 '16 at 20:59
0

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.