Passing Multi-Value Parameter to Oracle Linked Server in SQL Server from SSRS

0
votes

I'm currently building a report in SSRS using a MSSQL datasource. In that datasource I have a linked Oracle server that I am pulling my base dataset from. I'm trying to pass a multi-value text parameter from SSRS to MSSQL which then will be used in an OPENQUERY to the Oracle DB. Put simply my query in MSSQL looks something like this:

DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT id, type, name FROM sample WHERE type IN (''''A'''',''''B'''')'
SET @SQL = 'SELECT * FROM OPENQUERY(oracledb,''' + @SQL + ''')'
EXEC (@SQL)

Typically with SSRS, using MSSQL, I can replace the

type IN (''''A'''',''''B'''')

with

type IN (@Parameter)

How would I go about doing this when passing the multivalue parameter to the above mentioned query since its using a linkedserver to oracle?

1
sql-serveroraclereporting-services

1 Answers

0
votes

This works for openquery to DB2, but I will point out you're missing a + after @SQL in your example. Once I added that it worked.

SET @SQL = 'SELECT * FROM OPENQUERY(oracledb,''' + @SQL +''')'