CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Arun Rahul
Arun Rahul

Reputation: 605

Making dynamic queries for linked server in SQL Server

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.

Upvotes: 2

Views: 789

Answers (1)

Novice Programmer
Novice Programmer

Reputation: 41

Just build the string query wihtout parameters.

USE [companyDB]
GO

DECLARE
    @companyName AS nvarchar(50)
    ,@id nvarchar(MAX)
    ,@query NVARCHAR(MAX)

SET @companyName = 'AMAZON'

DECLARE @idTable TABLE
(
    id INT
)

--Repace Server, UID and PWD
SET @query = 
N'SELECT 
    [id]
FROM OPENROWSET
(
    N''SQLNCLI''
    ,N''Server=10.111.1.111;UID=username;PWD=password123;''
    ,N''SELECT [id]
       FROM [companyDB]
       WHERE [name] = '''''+@companyName+'''''''
)'

INSERT INTO @idTable
EXECUTE (@query)

SELECT TOP 1
    @id = id
FROM @idTable

Upvotes: 2

Related Questions