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

Asked

Viewed 214 times

2

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

Message 137, Level 15, Status 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 error also occurs. What I am doing wrong?

sql sql-server linked-server
  • 1

    I think the problem there is that in the scope of EXECUTE(@OPENQRY) the variable @QtdReg is not declared.

1 answer

1


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

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

sp_executesql

Runs a Transact-SQL statement or batch that can be reused many times or that has been created dynamically. The Transact-SQL statement or batch can contain parameters entered.

Using sp_executesql

To run a string, we recommend you use the stored procedure sp_executesql instead of instruction EXECUTE. As this stored procedure supports parameter replacement, the sp_executesql is more versatile than EXECUTE; and how the sp_executesql generates more likely implementation plans to be reused by SQL Server, the sp_executesql is more efficient than EXECUTE.

Browser other questions tagged sql sql-server linked-server

You are not signed in. Login or sign up in order to post.