Linked SQL Server and FireBird: Error 'Must declare the scalar variable'

Question

Linked SQL Server and FireBird: Error 'Must declare the scalar variable'

Navigation

#1 by (0 votes)

1

I made a linked of my SQLSERVER with FIREBIRD . But at the time I pass a variable inside the query to return the record amount I get the error:

Message 137, Level 15, State 1, Line 1 Must declare the scalar variable "@QtdReg".

Script :

DECLARE @LINKED VARCHAR(50)
DECLARE @SQL_LINKED VARCHAR(7000)
DECLARE @OPENQRY VARCHAR(7000)
DECLARE @QtdReg VARCHAR(700)
declare @codpro varchar(10)

SET @codpro = '1000'
SET @LINKED = 'Mylinked'

SET @SQL_LINKED = 'SELECT count(1) as total FROM produto i WHERE CAST(I.codigoref AS VARCHAR(10)) = '''''+@CodPro+''''' and i.caixa=201 '

SET @OPENQRY = ' SELECT  @QtdReg  =  ItemFT.total FROM  Openquery('+@LINKED+', '''+@SQL_LINKED+''') ItemFT'

EXECUTE (@OPENQRY)
SELECT @QTDREG

I tried to use SP_executesql also the error occurs. What am I doing wrong?

sql sql-server linked-server

asked by anonymous 11.10.2017 / 03:49

1 answer

Java: Library koushikdutta.ion.Ion returning empty How to calculate Mbps?

score 0 · Accepted Answer

If you want to return the value in a variable, replace EXECUTE with sp_executesql :

EXECUTE sp_executesql @OPENQRY,
                      N'@QtdReg VARCHAR(7000) OUTPUT',
                      @QtdReg = @QtdReg OUTPUT;

sp_executesql

Executes a Transact-SQL statement or batch that can be reused many times or that was created dynamically. The Transact-SQL statement or batch may contain parameters entered.

Using sp_executesql

To execute a string, we recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE ; and since sp_executesql generates execution plans more likely to be reused by SQL Server , sp_executesql is more efficient than EXECUTE .