SQL Linked Server query with Parameters

问题:

I need to select value from SQL Linked Server & get it to loacal variable

This is what I've written so far:

DECLARE @SQLQUERY AS VARCHAR(1000)
DECLARE @FINALQUERY AS VARCHAR(1000)
DECLARE @OutVal AS VARCHAR(10)

SET @SQLQUERY = 'SELECT Field1 FROM Table1 WHERE Field2=' + CAST(@var1 AS VARCHAR) 
SET @FINALQUERY = 'SELECT @OutVal=Field1 FROM OPENQUERY(LINKEDSERVER,' + '''' + @SQLQUERY + '''' + ')'
EXEC(@finalQuery)

but this is wrong as it does not set the local variable(@OutVal).

回答1:

Instead of exec, use sp_execute_sql with an output parameter:

exec sp_executesql @FinalQuery, N'@OutVal output', @OutVal = @OutVal out

Since sp_executesql expects nvarchar parameters, be sure to change the definition of @FinalQuery to nvarchar(max).

回答2:

@OutVal in query string does not recognized as a variable. use a function or return table statement.