Overcome 1,000 items limit for IN clause in Oracle with SSRS

Overcome 1,000 items limit for IN clause in Oracle with SSRS In Oracle, there is a 1,000 items limit when using the IN clause. In order to overcome this, we can use table JOIN, having the list of items in a temp table or sort. But what happens when the list is for SSRS parameter? We can use a Common Table Expression (CTE) to solve this.

The following code is specific to Oracle, but it can be written for SQL Server too with tweak. This is just to show the idea.

WITH cteTempTbl AS (
	SELECT regexp_substr(txt,'[^,]+',1,level) AS ITM
	FROM (SELECT :prmSSRS AS txt FROM DUAL)
	CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (txt, '[^,]'))+1
)

The above code takes the list of items, comma separated (that's how SSRS pass the values) from the parameter, prmSSRS, and create a temp table (cteTempTbl).

To use this CTE, we can do a couple of things.

• Using JOIN

      SELECT A.*
      FROM TABLE A
      INNER JOIN cteTempTbl t ON t.ITM = A.ITM
      

• Using EXISTS (potentially better performance)

      SELECT A.*
      FROM TABLE A
      WHERE
      	EXISTS (SELECT 1 FROM cteTempTbl t WHERE t.ITM = A.ITM)
      

With the example above, we should be able to pass any number of items in the SSRS parameter dropdown list into the SQL query.