including parameters in OPENQUERY

Question

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

Matthaeus

A workaround is create a view with the openquery and then using the view in the join – Counterchange

Answer 1

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

Fandango

Using any of these examples, how do you retrieve the records returned from the exec command? – Athwartships

To retrieve records, I've always built a table variable or temp table of my result set, then used INSERT INTO @TableVariable EXEC sp_executeSql @TSQL – Windlass

@JamesChen, it's easiest to think about when you work backwards. Start with the OpenQuery's query: SELECT * FROM tab WHERE col = 'Y'. To pass that statement as a string to OpenQuery, all single quotes need escaped: SELECT * FROM OPENQUERY(Server, 'SELECT * FROM tab WHERE col = ''Y'' '). Then, to pass the SELECT using OpenQuery to Dynamic SQL, THOSE quotes must be escaped: EXEC sp_executeSQL 'SELECT * FROM OPENQUERY(Server, ''SELECT * FROM tab WHERE col = ''''Y'''' '')'. Hope this helps! – Windlass

(can't use @ in the comments so using 'at') Dynamic DAX with params... I prepared the DAX string but Openquery didn't work. Pass the Whole Query method worked. Ran my DAX through this: SET atDAX = REPLACE(atDAX, '''', '''''') and at the end I had to close the string off and add a final bracket... EXEC (atOPENQUERY + atDAX + '''' + ')') – Motionless

@Motionless you could, if you formatted the inline code as inline code (using backticks): SET @DAX = REPLACE(@DAX, '''', '''''') – Sainfoin

Bret why are you firing OPENQUERY via sp_executesql? Isn't it that you can either use OPENQUERY or use sp_executesql? – Sarsenet

Answer 2

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.

Zakarias

this pointed me down the right path to succeed in what I was trying to accomplish! Thank you! this should have more upvotes – Helwig

Answer 3

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)

Socioeconomic

Unfortunately it doesn't work, if you want to use OpenQuery in context with if, for example in if (SELECT Col1 FROM OPENQUERY('Select ...') > 0 ) BEGIN ... END – Mephistopheles

@Stefan - you can select from openquery and insert the results into a temporary table. From there your options open up a lot, of course. – Utta

Answer 4

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)

Emanuelemanuela

This only works for Scalar output. Try Xml or a Table variable and this is not going to work. – Altdorfer

Answer 5

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';

Henkel

Answer 6

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.

Sonasonant

Answer 7

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;

Bohn

Answer 8

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

Subatomic

Answer 9

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

Gripe

This code needs a warning that A) field1 for ALL rows of TABLENAME will be passed across from the linked server - a potentially very expensive operation. B) the INNER JOIN could also be 'very expensive' – Cynthiacynthie

Answer 10

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]) ')

Convincing

Answer 11

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.

Arenicolous

Answer 12

SET NOCOUNT ON;
DECLARE @qry NVARCHAR(MAX); 

SELECT @qry=concat('select * from openquery(nube,',char(39),'call DATABASE.STORE_PROCEDURE(',@year,',',@month,',',@day,',"',@place,'")',char(39),')');
EXECUTE sp_executesql @qry;

be sure that create before the params

Saponify

Answer 13

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]

Hapten

Keyword is openquery dude :) – Celenacelene

it is an alternate approach. Whoever knows openquery should know this approach as well. It is also much cleaner. So +1 from my side. – Henkel

Answer 14

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

Session

Your answer uses literal SQL, but the OP wants to use a parameter (note his/her reference to field1=@someParameter). – Nonobservance

Answer 15

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)

Hillel