sql - 通过 Excel 单元格向 OPENQUERY SQL 添加参数

Question

我有一个在 Excel 中工作的 SQL 查询，但它使用以下代码。

SET QUOTED_IDENTIFIER OFF

SELECT * 
FROM OPENQUERY (
    INSQL, 
    "SELECT DateTime = convert(nvarchar, DateTime, 21), [TagName]
     FROM WideHistory
     WHERE wwRetrievalMode = 'Cyclic'
           AND wwCycleCount = 100
           AND wwVersion = 'Latest'
           AND DateTime >= '20161122 08:33:00.000'
           AND DateTime <= '20161122 08:38:00.000'"
)

我正在尝试添加 DateTime通过 Excel 单元格。通过论坛阅读，它提到参数化 OPENQUERY不像 Excel 中的普通 SQL 查询那样简单。

我可以做些什么来链接 Excel 中的一个单元格，我可以从中更改这些参数吗？

Answer 1

文档:OPENQUERY (Transact-SQL) :

Syntax OPENQUERY ( linked_server ,'query' ). OPENQUERY does notaccept variables for its arguments.

' query ' -Is the query string

将查询创建为字符串并执行

DECLARE @dt_start DATETIME = '2016-11-22 08:33:00.000';
DECLARE @dt_end DATETIME = '2016-11-22 08:38:00.000';

DECLARE @query VARCHAR(MAX);
DECLARE @openquery VARCHAR(MAX);

SELECT @query = 'SELECT DateTime = convert(nvarchar, DateTime, 21), [TagName]
                 FROM WideHistory
                 WHERE wwRetrievalMode = ''Cyclic''
                 AND wwCycleCount = 100
                 AND wwVersion = ''Latest''
                 AND DateTime >= ''' + CONVERT(VARCHAR(23), @dt_start, 121) + '''
                 AND DateTime <= ''' + CONVERT(VARCHAR(23), @dt_end, 121) + '''';

 --SELECT @query; 

SELECT @openquery = 'SELECT * FROM OPENQUERY (INSQL, ''' + REPLACE(@query, '''', '''''') + ''')';

 --SELECT @openquery;

EXEC (@openquery);