Passing multiple value parameters in SSRS to stored procedure

Question

I am trying to pass a multiple value string parameter to a table type parameter in a SQL Server 2012 stored procedure. I paste this code in the dataset of SSRS:

DECLARE @mylist clinic_list_tbltype
INSERT @mylist(n) VALUES (@pm_ChooseClinics)
EXEC sp_Skillset_Summary_With_Callbacks_Report @mylist, @pm_ChooseInterval, @pm_StartDateTime, @pm_EndDateTime

clinic_list_tbltype is a table type I created with one varchar(50) field named "n". I can call this stored procedure from SSMS o.k. like this (and it comes back very fast):

DECLARE @mylist clinic_list_tbltype
INSERT @mylist(n) VALUES ('clinicA'), ('clinicB')
EXEC sp_Skillset_Summary_With_Callbacks_Report  @mylist, 'Daily', '6/3/2014', '6/9/2014'

I can run in SSRS for only one clinic (but very slow), but if I try more than one it gives an error saying that

there are fewer columns in the INSERT statement than values specified in the Values clause

. Even running for one clnic it works, but it takes a very very long time compared to running the query in SSMS. Like 2 minutes vs. 1 second. Must be because I'm passing ('clinicA', 'clinicB') instead of ('clinicA'), ('clinicB').

How to do?

Answer 1

Right I need to give you some back ground 1st.

When you allow SSRS parameter to select multiple values, The selection of multiple values creates a comma deliminated string of value as one string

  'value1,value2,value3'

To check values in a string using IN operator we need strings concatenated with commas something like this ....

  'value1','value2','value3'

Your Proc

Now in your proc when you insert values explicitly it inserts multiple values into your table.

  INSERT INTO Table_Value_Param
  VALUES ('value1'),       --<-- 1st value/Row
         ('value2'),       --<-- 2nd Value/Row
         ('value3')        --<-- 3rd Value/Row

and this gives you back the expected results as when inside your procedure you execute a statement like

SELECT * 
FROM Table_Name
WHERE ColumnName IN (SELECT ColumnName 
                     FROM Table_Value_Param)

On the other hand when you try to insert into table using SSRS report Parameter you table inserts value like

  INSERT INTO Table_Value_Param
  VALUES ('value1,value2,value3')   --<-- One Row/Value containing all the values comma separated

Solution

Creating TVP in this situation doesnt really help, What I do is make use of dbo.Split() function inside my procedure.

You can find many definitions for split function online, for a some cool ones have a look here Split Function equivalent in tsql?

Once you have created this split function just use this function inside your procedure definition you dont even need the Table valued parameters then.

Something like this...

  SELECT * 
  FROM Table_Name 
  WHERE ColumnName IN ( 
                       SELECT Value
                       FROM dbo.Split(@Report_Param, ',')
                       )