Including Parameters in Openquery

including parameters in OPENQUERY

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

Including list of params in OPENQUERY

Before running your query with EXEC, it's best to just check first how the generated SQL string will look like.

For example by selecting the variable

DECLARE @TSQL VARCHAR(8000), @VAR CHAR(2)
DECLARE @Prefixos VARCHAR(MAX);

WITH cte AS 
(
    SELECT DISTINCT prefixo 
    FROM (values ('1'),('2'),('3')) q(prefixo)
)
SELECT @Prefixos = COALESCE(@Prefixos + ', ', '') + prefixo 
FROM cte 
ORDER BY prefixo

SELECT  @TSQL = 'SELECT * FROM OPENQUERY(DICOI_LINKEDSERVER,''SELECT * FROM ssr.vw_sigas_diage where cd_prf_responsavel in ('''''  + @Prefixos + ''''''') order by cd_prf_responsavel, codigo'

select @TSQL as TSQL
-- EXEC (@TSQL)

Then you can visually check if there's something odd about it.

Or just try running that sql yourself and see if it fails or not.

From that T-SQL it returns this result :

SELECT * FROM OPENQUERY(DICOI_LINKEDSERVER,'SELECT * FROM ssr.vw_sigas_diage where cd_prf_responsavel in (''1, 2, 3''') order by cd_prf_responsavel, codigo

Notice that there's a bit to many single-quotes in that string.
When using an IN with numbers, the single-quotes are not needed.
And something is missing at the end.

...  in ('+ @Prefixos +') order by cd_prf_responsavel, codigo'');';

Passing sql parameter to openquery's query

This is a duplicate post.

The short answer is OPENQUERY does not accept variables for its arguments. See: including parameters in OPENQUERY for a possible workaround.

How to pass int parameter to OpenQuery sql

Just Cast the INT value to Varchar before appending

DECLARE @Filtro int, @SQL NVARCHAR(MAX);
SET @Filtro = 1;
SET @SQL ='SELECT *
FROM OpenQuery(MACPAC, ''SELECT FET001.ET0109,  count(FET001.ET0101)
FROM AUTO.D805DATPOR.FET001 FET001
WHERE (FET001.ET0104=''''POE'''') AND (FET001.ET0105=''''DIS'''')
GROUP BY FET001.ET0109
HAVING COUNT (FET001.ET0101) > ''''' + RTRIM(LTRIM(CAST(@Filtro AS VARCHAR(20)))) + ''''''')';
EXEC sp_executesql @SQL;

How can I use a variable inside OPENQUERY

I just found the answer for my question, so I thought sharing it with you.

DECLARE @QUERY VARCHAR(MAX)
DECLARE @TSQL VARCHAR(100)
DECLARE @SP VARCHAR(50)

DECLARE @PARAMETERS VARCHAR(MAX)
DECLARE @PARAM1 VARCHAR(50)
DECLARE @PARAM2 VARCHAR(50)

SET @TSQL = N'SELECT * FROM OPENQUERY([192.168.1.1], ''SET FMTONLY OFF; '
SET @SP   = 'EXEC spNewTest '

SET @PARAM1 = '@Type='''+ QUOTENAME('Test','''') + ''''
SET @PARAM2 = '@Year='''+ QUOTENAME('2016','''') + ''''
SET @PARAMETERS = @PARAM1 + ', ' + @PARAM2

SET @QUERY = @TSQL + @SP + @PARAMETERS + ''')'

EXECUTE (@QUERY)

Thanks

Pass a table as parameter in openquery

The table variable cannot be passed as a parameter to OPENQUERY. As my table had only a single column I was able to convert it to a string and pass the string to OPENQUERY as a parameter. I did this within a procedure that accepts a table variable. After parsing the table to a string, the procedure also runs the OPENQUERY.

CREATE PROCEDURE  PRM_LIST ( @TableVariable LooseTimecardsTableType  READONLY)
AS
DECLARE
@LBKY_NVAR NVARCHAR(2500),
@POINTER INT,
@SQLString NVARCHAR(max)

SELECT @POINTER =  MIN(LABORKEY) FROM @TableVariable
WHILE @POINTER IS NOT NULL
BEGIN
SET @LBKY_NVAR = IIF(@LBKY_NVAR IS NULL,'('+''''+ CONVERT(VARCHAR,@POINTER) + '''',
@LBKY_NVAR + ',' + ''''+ CONVERT(VARCHAR,@POINTER) + '''')
SELECT @POINTER =  MIN(LABORKEY) FROM @TableVariable WHERE LABORKEY > @POINTER
END
SET @LBKY_NVAR = @LBKY_NVAR +  ')'
SET @LBKY_NVAR = REPLACE(@LBKY_NVAR,'''','''''')

SET @SQLString =
N'SELECT * FROM OPENQUERY(REMOTE, ''SELECT DISTINCT
DA.USERNAME, DA.WORKORDERLABORKEY, LB.PERFORMEDBY 
FROM
REMOTE.WORKORDERDETAILAUDITS DA
JOIN REMOTE.WORKORDERLABORBASE LB ON LB.LABORKEY = DA.WORKORDERLABORKEY
WHERE DA.WORKORDERAUDITCATEGORY = 0
AND LB.LABORKEY IN '+CAST(@LBKY_NVAR AS nvarchar(2500))+ N'
'')'
EXEC (@SQLString)
GO

The procedure call is:

EXECUTE PRM_LIST @DATATABLE

---

Related Topics