SQL SELECT * FROM OPENROWSET 带变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16607433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SELECT * FROM OPENROWSET with Variable
提问by user2393602
I am trying to pass a variable into a SELECT statement in OPENROWSET but I keep getting an error
我试图将一个变量传递给 OPENROWSET 中的 SELECT 语句，但我一直收到错误
DECLARE @dDateTIME DATE
SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)
INSERT INTO dbo.tblSales
SELECT * FROM OPENROWSET('MSDASQL', 'dsn=mydsn;uid=myid;pwd=mypwd;',
'SELECT
ID,
TranDate,
ProductID,
CostValue,
SalesValue,
QtySold,
FROM tblSales WHERE TranDate='' + @dDateTIME + ''')
DECLARE @dDateTIME DATE
SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)
DECLARE @SQL NVARCHAR(1024) =
'SELECT
ID,
TranDate,
ProductID,
CostValue,
SalesValue,
QtySold,
FROM tblSales WHERE TranDate=''' + CAST(@dDateTIME AS VARCHAR(64)) + ''''
DECLARE @RunSQL NVARCHAR(max)
SET @RunSQL=
'SELECT * FROM OPENROWSET (''MSDASQL'', ''dsn=mydsn;uid=myid;pwd=mypwd;'',''EXEC @SQL'')'`
What syntax do I use to apply to @SQL?
我使用什么语法来应用@SQL？
The error I get is:
我得到的错误是：
The error is :OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in predicate: TranDate = '(SELECT @dDateTIME)' "
错误是：链接服务器“(null)”的OLE DB 提供程序“MSDASQL”返回消息“[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]谓词错误：TranDate = '(SELECT @ dDateTIME)'"
回答by Alex K.
Your variable is not being concatenated to the string (its '''to close a string with a ') to correct this (and perform the necessary type conversion):
您的变量没有连接到字符串（它'''用 a 关闭字符串'）来纠正这个问题（并执行必要的类型转换）：
DECLARE @SQL NVARCHAR(1024) =
'SELECT
ID,
TranDate,
ProductID,
CostValue,
SalesValue,
QtySold,
FROM tblSales WHERE TranDate=''' + CAST(@dDateTIME AS VARCHAR(64)) + ''''
Additionally you cannot use an expression or variable with OPENROWSETso you are going to need to call it via EXEC()/sp_executeSQL, see; Using a Variable in OPENROWSET Query
此外，您不能使用表达式或变量 with，OPENROWSET因此您需要通过EXEC()/调用它sp_executeSQL，请参阅；在 OPENROWSET 查询中使用变量
回答by Aleksandr Fedorenko
You can dynamically create SQL statement and then run that command.
您可以动态创建 SQL 语句，然后运行该命令。
DECLARE @dDateTIME DATE,
@RunSQL NVARCHAR(max)
SET @dDateTIME = (SELECT SalesDate FROM dbo.SalesDate)
SELECT @RunSQL =
'INSERT INTO dbo.tblSales
SELECT * FROM OPENROWSET(''MSDASQL'', ''dsn=mydsn;uid=myid;pwd=mypwd;'',
''SELECT
ID,
TranDate,
ProductID,
CostValue,
SalesValue,
QtySold,
FROM tblSales WHERE TranDate=''''' + CONVERT(nvarchar, @dDateTIME, 112) + ''''''')'
--PRINT @RunSQL
EXEC sp_executesql @RunSQL
