将参数传递给 OPENQUERY [英] 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)

我应该如何传递参数 @StoreId @StartDate 和 @EndDate 以使其正常工作?谢谢.

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

推荐答案

OPENQUERY 需要文字；不能是表达式.如果需要传递参数.一种方法是使用动态 SQL，但它可能会变得丑陋".这是不完整的，因为我们拥有的是

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;

因此另一种方法是使用EXECUTE ... AT，它需要一个链接服务器:

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}];

这篇关于将参数传递给 OPENQUERY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！