• Marek Vavrovic

Stored Procedure with IN operator in SSRS

Data source: sample data

Data set: stored procedure


Create PROC dbo.uspSales

@Region nvarchar(50)

AS

BEGIN

SET NOCOUNT ON;

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]

,[Region]

,[Representant]

,[Category]

,[Item]

,[Units]

,[UnitCost]

,[Total]

INTO ##TempResult

FROM [dbo].[salesCat]

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


EXECUTE (@SQLcode)

SELECT * FROM ##TempResult

DROP TABLE ##TempResult


END


--------------------------------------------

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