• Marek Vavrovic

Stored Procedure with IN operator in SSRS

Data source: sample data

Data set: stored procedure

Create PROC dbo.uspSales

@Region nvarchar(50)




DECLARE @pRegion nvarchar(max)

SET @pRegion = REPLACE(@Region,',',''',''')

DECLARE @SQLCode varchar(max)

--place result set into a temporary table to display the fields

SET @SQLCode='

SELECT Year([OrderDate])[OrderDate]








INTO ##TempResult

FROM [dbo].[salesCat]

where Region in ('''+@pRegion+''')'


SELECT * FROM ##TempResult

DROP TABLE ##TempResult



Exec dbo.uspSales 'Europe,America'

Using a dynamic query and global temporally table. If I would use just the dynamic SQL query, SSRS won't display the fields at all.

Step 1

Add dataset, click on Stored Procedure and find it on the list. Click Refresh Fields button, provide a value for the parameter to display the fields.

Step 2

Add second dataset. This will be the input for the parameter.

Step 3

Adding second dataset

Step 4

Parameter properties

  1. GENERAL: Allow multiple values

  2. AVAILABLE VALUES: Get values from a query

Test the report

3 views0 comments