SqlServer中创建Oracle连接服务器（二）SQL 在OPENQUERY中使用参数

OpenQuery 是SQL Server用来与其他Server交互的一种技术，通过OpenQuery，SQL Server 可以直接访问其他数据库资源。而其他数据库在OpenQuery 表达式中是以Linked Server 存在的。 使用sp_linkedservers 可以找到当前数据库的所有linked server. OpenQuery的表达式可以这么写：

Select * from OpenQuery（[linkedServerName],'Select * from table1 where rownum < 10')
而且值得注意的是，如果，你要在OpenQuery中执行如下的数据库语句，

Select * from tbl_emp Where empName = 'leo'
'leo' 必须以双引号的形式传人，如下，

Select * from OpenQuery（[linkedServerName],'Select * from table1 where empName = ''leo''')

 

sp_executesql 或 EXECUTE 语句执行字符串时，字符串被作为其自包含批处理执行。SQL Server 将Transact-SQL 语句或字符串中的语句编译进一个执行计划，该执行计划独立于包含 sp_executesql 或 EXECUTE 语句的批处理的执行计划。下列规则适用于自含的批处理：
直到执行 sp_executesql 或EXECUTE 语句时才将sp_executesql 或 EXECUTE 字符串中的 Transact-SQL 语句编译进执行计划。执行字符串时才开始分析或检查其错误。执行时才对字符串中引用的名称进行解析。
执行的字符串中的 Transact-SQL 语句，不能访问 sp_executesql 或 EXECUTE 语句所在批处理中声明的任何变量。包含 sp_executesql 或 EXECUTE 语句的批处理不能访问执行的字符串中定义的变量或局部游标。
如果执行字符串有更改数据库上下文的 USE 语句，则对数据库上下文的更改仅持续到 sp_executesql 或 EXECUTE 语句完成。

sp_executesql 支持对 Transact-SQL 字符串中指定的任何参数的参数值进行替换，但是 EXECUTE 语句不支持。因此，由 sp_executesql 生成的 Transact-SQL 字符串比由 EXECUTE 语句所生成的更相似。SQL Server 查询优化器可能将来自 sp_executesql 的 Transact-SQL 语句与以前所执行的语句的执行计划相匹配，以节约编译新的执行计划的开销。

OPENQUERY里面带参数查询

DECLARE @Sql VARCHAR(1000)
DECLARE @organizationID VARCHAR(10)
SELECT @organizationID = (SELECT ORGANIZATION_ID FROM MYORGS WHERE ORGANIZATION_NAME = 'MMT')
SET @Sql = 'SELECT * from tableName where organization_id ='+@organizationID 
SET @Sql = 'SELECT * FROM OPENQUERY(BETSYCRP2, ''' + REPLACE(@Sql, '''', '''''') + ''')'
EXEC(@Sql)

OPENQUERY返回参数查询

DECLARE @EXECSQL Nvarchar(1000) -- 要执行的动态SQL
DECLARE @ORLSQL Nvarchar(1000) -- ORACLE查询语句
DECLARE @TEST_NAME VARCHAR(100) -- 条件变量
DECLARE @RCOUNT INT -- 返回值

SET @TEST_NAME='03'
SET @RCOUNT=0
SET @ORLSQL='''SELECT COUNT(*) FROM ALINK_SERVER_TEST WHERE TEST_NAME='+@TEST_NAME+''''
--SET @EXECSQL='SELECT @a=(SELECT * FROM OPENQUERY(ORACLETEST, '+@ORLSQL+' ))'
--上面一行在SQL server中报错，改为下面代码
SET @EXECSQL='SELECT @a=(SELECT * FROM OPENQUERY(ORACLETEST, ''' + REPLACE(@ORLSQL, '''', '''''') + '''))'
exec sp_executesql @EXECSQL,N'@a int output', @RCOUNT OUTPUT
PRINT @RCOUNT