SSRS: Multi-valued Parameter as Stored Procedure Input

SSRS: Multi-valued Parameter as Stored Procedure Input

Few months back I wrote an article on Multi-valued Parameter as Stored procedure Input, which will work only for single character multi-valued input parameter .

Now let us see an example for SSRS report with Multi-valued Parameter as Stored procedure Input, which will work in all cases.

Example:

Create Stored procedure in AdventureWorks database as shown in the following code block:

USE AdventureWorks2012
GO
  
CREATE PROCEDURE Shift_Get
@ShiftName NVARCHAR(500)
AS
BEGIN
;WITH CTE1
AS
(
SELECT CAST(('<i><j>' + REPLACE(@ShiftName, ',', '</j></i><i><j>') + '</j></i>' ) AS XML) CSV
),CTE2
AS
(
SELECT CAST(i.query('./text()') AS VARCHAR(100))CSV
FROM CTE1 
CROSS APPLY CSV.nodes('/i/j') As x(i)
)
SELECT * FROM HumanResources.Shift 
WHERE Name IN (SELECT * FROM CTE2)
END

Create Dataset pointing to this newly created Stored procedure in AdventureWorks database as shown below :



Create Dataset for Multi-valued input parameter as shown below :



Follow report parameter settings as shown in these images:






From Toolbox - > Report Items - > drag and drop Table on to the report body under Design pane
and then assign the column values with fields from dataset (Report_Dataset).

Now click on Preview pane, select the parameter values and click on View Report :

  

Note : I didn't make use of SSRS JOIN function .


See Also



Leave a Comment
  • Please add 1 and 3 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 2. Comment: Formatting

  • SathyanarrayananS edited Original. Comment: minor edit

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • SathyanarrayananS edited Original. Comment: minor edit

  • Naomi  N edited Revision 2. Comment: Formatting

  • It may be a good idea to add See Also section to this (and other articles) and link to your other SSRS articles.

  • Also, why do you use nvarchar(500) parameter? Why not nvarchar(max)?

  • Hi Naomi,

    Do we have anything like consolidated lists for SSRS ,like we have for T-SQL -  social.technet.microsoft.com/.../17785.sql-server-query-language-transact-sql.aspx

  • Considering this example , the input parameter can be maximum of

    select len('day,night,evening') . I guess , I had given enough size for input parameter , let me know if it needs to be changed !!

  • Nice article.

    * the splitting part in the input parameter @ShiftName better to do using CLR SPLIT function. this is a must function in any server i think! If you are not convinced by my recommendation then you can check this link: www.sqlperformance.com/.../split-strings

  • Hi Pituach,

    Thanks for sharing very good article on splitting technique !!!

    I will recommed and implement CLR SPLIT function in my future articles :)

Page 1 of 1 (8 items)