CODEWITHSUNDEEP
sql-serverstored-procedures
Ozan Yurtsever
Ozan Yurtsever

Reputation: 1304

Passing parameters to a OPENQUERY

Why is the following query does not work? It gives me error: Incorrect syntax near '+'.

SELECT  *
INTO    #tmpTable
FROM OPENQUERY("127.0.0.1", 'EXEC [DB].dbo.SP_inventory' +  @StoreId + ',' + @StartDate ',' +  @EndDate)

How should I be passing the parameters @StoreId @StartDate and @EndDate to make it work correctly? Thanks.

Upvotes: 1

Views: 699

Answers (1)

Thom A
Thom A

Reputation: 95554

OPENQUERY requires a literal; it can't be an expression. If you need to pass parameters. one method is using dynamic SQL, but it can get "ugly". This is incomplete, as what we have is however

DECLARE @StoreId int = 7,
        @StartDate date = '20190101',
        @EndDate date = '20190701';
--Values shoukd be set

DECLARE @SQL nvarchar(MAX);
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'{SELECT Statement parts}' + @CRLF + 
           N'FROM OPENQUERY("172.16.111.11", N''EXEC [DB].dbo.SP_inventory' +  CONVERT(varchar(10),@StoreId) + ',' + QUOTENAME(CONVERT(varchar,@StartDate,112),'''') + ',' +  QUOTENAME(CONVERT(varchar,@EndDate,112),'''') +') OQ';

PRINT @SQL; --Your best Friend
EXEC sp_executesql @SQL;

Therefore an alternative methhod is using EXECUTE ... AT, which requires a linked server:

EXEC (N'[DB].dbo.SP_inventory ?, ?, ?;',@StoreId, @StartDate, @EndDate) AT [{Linked Server Name}];

Upvotes: 3

Related Questions