包括OPENQUERY中的参数

包括OPENQUERY中的参数

本文介绍了包括OPENQUERY中的参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何在sql openquery中使用参数,例如:

How can I use a parameter inside sql openquery, such as:

SELECT * FROM OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME
where field1=@someParameter') T1 INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME
T2 ON T1.PK = T2.PK

推荐答案

来自 OPENQUERY 文档指出:

有关解决方法,请参见此文章.

See this article for a workaround.

更新:

根据建议,我将包括以下文章中的建议.

As suggested, I'm including the recommendations from the article below.

传递基本值

当基本的Transact-SQL语句已知但您必须传递一个或多个特定值时,请使用类似于以下示例的代码:

When the basic Transact-SQL statement is known, but you have to pass in one or more specific values, use code that is similar to the following sample:

DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT  @VAR = 'CA'
SELECT  @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'
EXEC (@TSQL)

传递整个查询

当您必须传递整个Transact-SQL查询或链接服务器的名称(或两者都传递)时,请使用类似于以下示例的代码:

When you have to pass in the whole Transact-SQL query or the name of the linked server (or both), use code that is similar to the following sample:

DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'MyLinkedServer'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT au_lname, au_id FROM pubs..authors'')'
EXEC (@OPENQUERY+@TSQL)

使用Sp_executesql存储过程

为避免多层引号,请使用类似于以下示例的代码:

To avoid the multi-layered quotes, use code that is similar to the following sample:

DECLARE @VAR char(2)
SELECT  @VAR = 'CA'
EXEC MyLinkedServer.master.dbo.sp_executesql
N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
N'@state char(2)',
@VAR

这篇关于包括OPENQUERY中的参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-25 23:46