SQL 在 OPENROWSET 查询中使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13831472/
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
Using a Variable in OPENROWSET Query
提问by Control Freak
I'm having trouble with this query:
我遇到了这个查询的问题：
SELECT *
FROM OPENROWSET(
'SQLNCLI',
'DRIVER={SQL Server};',
'EXEC dbo.sProc1 @ID = ' + @id
)
Gives an error:
给出一个错误：
Incorrect syntax near '+'.
'+' 附近的语法不正确。
Anyone know why I'm getting this error?
有谁知道我为什么会收到这个错误？
回答by praveen
As suggested by Scott , you cannot use expressions in OPENROWSET.Try creating a dynamic sql to pass the parameters
正如 Scott 所建议的，您不能在OPENROWSET.Try 创建一个动态 sql 来传递参数中使用表达式
Declare @ID int
Declare @sql nvarchar(max)
Set @ID=1
Set @sql='SELECT *
FROM OPENROWSET(
''SQLNCLI'',
''DRIVER={SQL Server};'',
''EXEC dbo.usp_SO @ID =' + convert(varchar(10),@ID) + ''')'
-- Print @sql
Exec(@sql)
回答by Scott
OPENROWSET requires string literals, not expressions. It's complaining about the plus sign, becaue it doesn't expect anything more than a string literal and you follewed the string literal with an operator.
OPENROWSET 需要字符串文字，而不是表达式。它在抱怨加号，因为它不期望比字符串文字更多的东西，并且您使用运算符跟随字符串文字。
See http://msdn.microsoft.com/en-us/library/ms190312.aspxwhich states:
请参阅http://msdn.microsoft.com/en-us/library/ms190312.aspx，其中指出：
'query'
Is a string constant sent to and executed by the provider...
'询问'
是发送给提供者并由提供者执行的字符串常量...
回答by yordinet
Declare @Route VARCHAR(200)
Declare @sql nvarchar(max)
Set @Route='C:\OCRevisiones.xlsx;'
Set @sql='SELECT * INTO FFFF
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
''SELECT * FROM [Sheet1$]'')'
Print @sql
--Exec(@sql)
回答by harmath
If you need parameters you can also use sp_executesql:
如果您需要参数，您还可以使用sp_executesql：
BEGIN
DECLARE
@p_path varchar(200)='D:\Sample\test.xml',
@v_xmlfile xml,
@v_sql nvarchar(1000)
SET @v_sql=N'select @v_xmlfile= CONVERT(XML, BulkColumn) FROM
OPENROWSET(BULK '''+@p_path+''', SINGLE_BLOB) AS x;'
EXEC sp_executesql @v_sql,N'@v_xmlfile xml output',@v_xmlfile output;
SELECT @v_xmlfile
END
回答by roblem
For what it is worth.. The reason we use openrowset rather than a straight linked server query is that the processing for a linked server query happens on the local server. (Slow and often brings most of the table back)
对于它的价值.. 我们使用 openrowset 而不是直接链接服务器查询的原因是链接服务器查询的处理发生在本地服务器上。（缓慢并且经常将大部分桌子带回来）
Yes we can do the string concatination as above.
是的，我们可以像上面那样进行字符串连接。
A different option where you have ease of syntax and the power of parameters.
一个不同的选项，您可以使用简单的语法和强大的参数。
Create a stored proc on the remote box, that proc has all the parameters you need. Call the stored proc from with a standard linked server query (same perf or better than the above soultion and significantly easier to code with.
在远程盒子上创建一个存储过程，该过程具有您需要的所有参数。使用标准链接服务器查询调用存储过程（与上述解决方案相同或更好，并且更容易编码。
e.g. linkedservername.database.dbo.myproc 123,'abc','someparam',getdate()
例如，linkedservername.database.dbo.myproc 123,'abc','someparam',getdate()
Just an option....
只是一个选择....
