包括 OPENQUERY 中的参数

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

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

You can execute a string with OPENQUERY once you build it up. If you go this route think about security and take care not to concatenate user-entered text into your SQL!

DECLARE @Sql VARCHAR(8000)
SET @Sql = 'SELECT * FROM Tbl WHERE Field1 < ''someVal'' AND Field2 IN '+ @valueList
SET @Sql = 'SELECT * FROM OPENQUERY(SVRNAME, ''' + REPLACE(@Sql, '''', '''''') + ''')'
EXEC(@Sql)
小开

From the MSDN page:

OPENQUERY does not accept variables for its arguments

Fundamentally, this means you cannot issue a dynamic query. To achieve what your sample is attempting, try this:

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

Clearly if your TABLENAME table contains a large amount of data, this will go across the network too and performance might be poor. On the other hand, for a small amount of data, this works well and avoids the dynamic sql construction overheads (sql injection, escaping quotes) that an exec approach might require.

小开
最佳答案

From the OPENQUERY documentation it states that:

OPENQUERY does not accept variables for its arguments.

See this article for a workaround.

UPDATE:

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

Pass Basic Values

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)

Pass the Whole Query

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)

Use the Sp_executesql Stored Procedure

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
小开 
DECLARE @guid varchar(36);  select @guid= convert(varchar(36), NEWID() );
/*
The one caveat to this technique is that ##ContextSpecificGlobal__Temp should ALWAYS have the exact same columns.
So make up your global temp table name in the sproc you're using it in and only there!
In this example I wanted to pass in the name of a global temporary table dynamically.  I have 1 procedure dropping
off temporary data in whatever @TableSrc is and another procedure picking it up but we are dynamically passing
in the name of our pickup table as a parameter for OPENQUERY.
*/
IF ( OBJECT_ID('tempdb..##ContextSpecificGlobal__Temp' , 'U') IS NULL )
EXEC ('SELECT * INTO ##ContextSpecificGlobal__Temp FROM OPENQUERY(loopback, ''Select *,''''' +  @guid +''''' as tempid FROM ' + @TableSrc + ''')')
ELSE
EXEC ('INSERT ##ContextSpecificGlobal__Temp SELECT * FROM OPENQUERY(loopback, ''Select *,''''' +  @guid +''''' as tempid FROM ' + @TableSrc + ''')')


--If this proc is run frequently we could run into race conditions, that's why we are adding a guid and only deleting
--the data we added to ##ContextSpecificGlobal__Temp
SELECT * INTO #TableSrc FROM ##ContextSpecificGlobal__Temp WHERE tempid = @guid


BEGIN TRAN t1
IF ( OBJECT_ID('tempdb..##ContextSpecificGlobal__Temp' , 'U') IS NOT NULL )
BEGIN
-- Here we wipe out our left overs if there if everyones done eating the data
IF (SELECT COUNT(*) FROM ##ContextSpecificGlobal__Temp) = 0
DROP TABLE ##ContextSpecificGlobal__Temp
END
COMMIT TRAN t1


-- YEAH! Now I can use the data from my openquery without wrapping the whole !$#@$@ thing in a string.
小开

In the following example I'm passing a department parameter to a stored procedure(spIncreaseTotalsRpt) and at the same time I'm creating a temp table all from an OPENQUERY. The Temp table needs to be a global Temp (##) so it can be referenced outside it's intance. By using exec sp_executesql you can pass the department parameter.

Note: be careful when using sp_executeSQL. Also your admin might not have this option available to you.

Hope this helps someone. 

 IF OBJECT_ID('tempdb..##Temp') IS NOT NULL
/*Then it exists*/
begin
DROP TABLE ##Temp
end
Declare @Dept as nvarchar(20) ='''47'''


declare @OPENQUERY  as nvarchar(max)
set @OPENQUERY = 'Select ' + @Dept + ' AS Dept,  * into ##Temp from openquery(SQL_AWSPROD01,'''


declare @sql nvarchar(max)= @openquery +  'SET FMTONLY OFF EXECUTE SalaryCompensation.dbo.spIncreaseTotalsRpts ' + '''' + @Dept + ''''  + ''')'
declare @parmdef nvarchar(25)
DECLARE @param nvarchar(20)


SET @parmdef = N'@Dept varchar(20)'
-- select @sql
-- Print @sql + @parmdef  + @dept
exec sp_executesql @sql,@parmdef, @Dept
Select * from ##Temp

Results

Dept increase Cnt 0 1 2 3 4 5 6 0.0000 1.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000

小开

Actually, We found a way to do this:

DECLARE @username varchar(50)
SET @username = 'username'
DECLARE @Output as numeric(18,4)
DECLARE @OpenSelect As nvarchar(500)
SET @OpenSelect = '(SELECT @Output = CAST((CAST(pwdLastSet As bigint) / 864000000000) As numeric(18,4)) FROM OpenQuery (ADSI,''SELECT pwdLastSet
FROM  ''''LDAP://domain.net.intra/DC=domain,DC=net,DC=intra''''
WHERE objectClass =  ''''User'''' AND sAMAccountName = ''''' + @username + '''''
'') AS tblADSI)'
EXEC sp_executesql @OpenSelect, N'@Output numeric(18,4) out', @Output out
SELECT @Output As Outputs

This will assign the result of the OpenQuery execution, in the variable @Output.

We tested for Store procedure in MSSQL 2012, but should work with MSSQL 2008+.

Microsoft Says that sp_executesql(Transact-SQL): Applies to: SQL Server (SQL Server 2008 through current version), Windows Azure SQL Database (Initial release through current release). (http://msdn.microsoft.com/en-us/library/ms188001.aspx)

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

I figured out a way that works for me. It does require the use of a scratch table that a linked server has access to though.

I created a table and populated it with the values I need then I reference that table through a linked server.

SELECT *
FROM OPENQUERY(KHSSQLODSPRD,'SELECT *
FROM ABC.dbo.CLAIM A WITH (NOLOCK)
WHERE A.DOS >= (SELECT MAX(DATE) FROM KHSDASQL01.DA_MAIN.[dbo].[ALLFILENAMES]) ')
小开 
declare @p_Id varchar(10)
SET @p_Id = '40381'


EXECUTE ('BEGIN update TableName
set     ColumnName1 = null,
ColumnName2 = null,
ColumnName3 = null,
ColumnName4 = null
where   PERSONID = '+ @p_Id +'; END;') AT [linked_Server_Name]
小开

Simple example based off of @Tuan Zaidi's example above which seemed the easiest. Didn't know you can do the filter on the outside of OPENQUERY... so much easier!

However in my case I needed to stuff it in a variable so I created an additional Sub Query Level to return a single value.

SET @SFID = (SELECT T.Id FROM (SELECT Id,  Contact_ID_SQL__c  FROM OPENQUERY([TR-SF-PROD], 'SELECT Id,  Contact_ID_SQL__c FROM Contact') WHERE Contact_ID_SQL__c = @ContactID) T)
小开

Combine Dynamic SQL with OpenQuery. (This goes to a Teradata server)

DECLARE
@dayOfWk    TINYINT = DATEPART(DW, GETDATE()),
@qSQL       NVARCHAR(MAX) = '';


SET @qSQL = '
SELECT
*
FROM
OPENQUERY(TERASERVER,''
SELECT DISTINCT
CASE
WHEN ' + CAST(@dayOfWk AS NCHAR(1)) + ' = 2
THEN ''''Monday''''
ELSE ''''Not Monday''''
END
'');';


EXEC sp_executesql @qSQL;
小开

We can use execute method instead of openquery. Its code is much cleaner. I had to get linked server query result in a variable. I used following code.

CREATE TABLE #selected_store
(
code VARCHAR(250),
id INT
)
declare @storeId as integer = 25
insert into #selected_store (id, code) execute('SELECT store_id, code from quickstartproductionnew.store where store_id = ?', @storeId) at [MYSQL]


declare @code as varchar(100)
select @code = code from #selected_store
select @code
drop table #selected_store

Note:

if your query doesn't work, please make sure remote proc transaction promotion is set as false for your linked server connection.

EXEC master.dbo.sp_serveroption
@server = N'{linked server name}',
@optname = N'remote proc transaction promotion',
@optvalue = N'false';
小开

Just try it this way, should work, easy! In your WHERE clause, after column name and equal to sign:- add TWO single quotes, your search value and then THREE single quotes. Close the bracket.

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

小开 
DECLARE @usernames NVARCHAR(MAX);
SET @usernames = N'';
DECLARE @len INT;
SELECT @usernames = @usernames + ISNULL(username + ''''',''''', '')
FROM #tempusername;
SET @len = len(@usernames);
SET @usernames = N'''''' + LEFT(@usernames, @len - 3);
DECLARE @sql NVARCHAR(MAX);
SET @sql
= N'SELECT *
FROM OPENQUERY
([Linked Server],
''SELECT username
FROM [MySQL Database].[Table Name]
WHERE username IN ( ' + @usernames + N')'')';

I found this workaround on this link https://social.technet.microsoft.com/Forums/en-US/98e0c17a-8ead-4633-a053-51c49b313bd3/how-can-i-use-a-table-variable-as-a-parameter-for-openquery?forum=sqltools&prof=required it was very helpful.


提交答案