Sql-server – Making dynamic queries for linked server in SQL Server

dynamic-sqllinked-serversql serversyntax

I want to create a dynamic query in SQL Server which will run on linked server. I am trying to do it as follows.

USE [MYDB]
GO

DECLARE @company AS nvarchar(50);
DECLARE @id nvarchar(MAX);
DECLARE @query nvarchar(MAX);

SET @company = 'mycompany.com';
SET @query = N'SELECT @csid = id FROM OPENQUERY(LINKSERVER12, 
   ''SELECT id from company where name = @comp'')';
EXECUTE sp_executesql @company_query, N'@comp nvarchar(50), @csid 
nvarchar(MAX) OUTPUT', @comp = @company,@csid = @id OUTPUT

In the above script, I want to pass the value for @comp dynamically. For that I tried setting input and output variable while executing SQL with sp_executesql.

I am getting the following error

Syntax error in SQL statement. Syntax error line 1 at or after token .[10179].

Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query "SELECT id from company where name = @comp" for execution against OLE DB provider "MSDASQL" for linked server "LINKSERVER12".

The error is happening at the dynamic query

N'SELECT @csid = id FROM OPENQUERY(LINKSERVER12, 
   ''SELECT id from company where name = @comp'')'

I tried replacing @comp in the SQL query with ''@comp'', ''''@comp'''' with no luck. Any help is greatly appreciated.

Best Answer

You can do it this way:

DECLARE @company AS nvarchar(50);
DECLARE @id nvarchar(MAX);
DECLARE @query nvarchar(MAX) = N'SELECT @id = id from company where name = @comp';

exec [LINKSERVER12].[MYDB].sys.sp_executesql @query, N'@comp nvarchar(50), @id nvarchar(MAX) OUTPUT', @comp = @company,@id = @id OUTPUT;

select @id;
Related Question