Sql-server – Out Param With OpenQuery

sql serversql-server-2008-r2t-sql

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

Best Answer

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

Related Solutions

Sql-server – use parameters in a Cte

The first error is on Line 5, telling you that you cannot use Functions in your parameter's default values.

Pass in NULL as the default value, then sort the parameters out inside the Stored Procedure:

CREATE PROCEDURE dbo.Test
(
  @StartDate As datetime = NULL 
  ,@EndDate As datetime = NULL 
)
AS

BEGIN

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -8, CAST(GETDATE() AS DATE))
IF @StartDate IS NULL
  SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0);

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -1, @StartDate)
IF @EndDate IS NULL
  SET @EndDate = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0);


END

Once you have fixed this issue, and capitalise the @startDate parameter to read @StartDate, you should be fine.

For reference, there's an open Connect Item (currently 7 years old) that is Microsoft acknowledging it's on their 'ToDo' list.

Sql-server – Must declare the scalar variable

Execution of the variable @sqlStr is done under a separate context than that in which you are currently running. Therefore the variables that you have declared in your session will not be available.

Try this instead:

declare @params = N'@gID smallint';
exec sp_executesql @sqlStr, @params, @gID = @gID;

Documentation for sp_executesql can be found here:

https://msdn.microsoft.com/en-us/library/ms188001.aspx

Full code example:

declare @dbName varchar (50) = 'student';
declare @fldName varchar (50) = 'stID';
declare @gID smallint = 4;
declare @params nvarchar(max) = N'@gID smallint';
declare @sqlstr nvarchar(max) = 
        N'select max([' + @fldName +'])
            from [dbo].['+ @dbName +'] 
           where [dbo].['+ @dbName +'].[gID ] = @gID';
exec sp_executesql @sqlStr, @params, @gID = @gID;

Best Answer

Related Solutions

Sql-server – use parameters in a Cte

Sql-server – Must declare the scalar variable

Related Question