sqlparametersopenquery

including parameters in 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

Solution

  • 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