SQL Linked Server query with Parameters

Question

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).

Andomar · Accepted Answer · 2011-08-26 09:25:18Z

2

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).

edited Aug 26, 2011 at 9:25

answered Aug 26, 2011 at 9:20

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xenedia · Accepted Answer · 2011-08-26 09:27:40Z

1

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

answered Aug 26, 2011 at 9:27

xenedia

3142 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL Linked Server query with Parameters

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related