SQL服务器变量字符串
问题描述:
我已经存储的过程,其发送列W /报价工程确定:SQL服务器变量字符串
ALTER PROCEDURE [dbo].[Proc] @Store char(5)
AS
BEGIN
SELECT * FROM OPENQUERY(Server, 'SELECT * FROM T WHERE Store = ''MA009'' GROUP BY Store')
END
如何在OPENQUERY字符串插入@Store?下面不工作
ALTER PROCEDURE [dbo].[Proc] @Store char(5)
AS
BEGIN
SELECT * FROM OPENQUERY(Server, 'SELECT * FROM T WHERE Store = '''+ @Store + ''' GROUP BY Store')
END
答
据MSDN,
OPENQUERY不接受参数变量。
但是,你可以尝试下面;
DECLARE @SQL varchar(8000), @Store char(5)
SELECT @Store = 'MA009'
SELECT @SQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM T WHERE Store =''''' + @Store + ''''' GROUP BY Store '')'
EXEC (@SQL)
或者您可以使用sp_executesql的存储过程
为了避免多层报价,使用代码是类似于下面的示例:
DECLARE @Store char(5)
SELECT @Store = 'MA009'
EXEC MyLinkedServer.master.dbo.sp_executesql
N'SELECT * FROM T WHERE Store = @var',
N'@var char(5)',
@Store
答
这是不可能的使用OPENQUERY中的变量。但你可以使用以下变通方法的变量:
DECLARE @test nvarchar(MAX);
SET @test = 'MyStringValue';
DECLARE @query nvarchar(max) =
'SELECT * FROM OPENQUERY(adamm_db,''Select *
FROM databaseName.schemaName.table
WHERE id = ''''' + @test + ''''''')'
EXEC(@query)
感谢第一个例子工作,但即时通讯报价可以帮助我,如果额外的引号添加? – Maxrem 2014-09-30 12:11:31