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?

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jun 10 '14 at 11:33
Possible duplicate of [Passing multiple values for a single parameter in Reporting Services](http://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services) — KyleMit, Mar 20 '17 at 19:57

score 7 · Answer 1 · edited May 23 '17 at 12:00

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, ',')
                       )

I'll have a look at that, thanks. Are you sure that SSRS would pass a multivalue parameter as 'value1,value2,value3'? I would expect both that and 'value1','value2','value3' to be incorrect, and given the error "...fewer columns...etc". I would expect it be the latter. If it was the former I would expect it to run, but bring back no data. — urbanmojo, Jun 11 '14 at 01:51
Know it's an old post but for anyone wondering @urbanmojo 's correct in that the multivalue parameter formatting is 'val1','val2'... I was just working on a query in SSRS and used it like: `WHERE ColumnName IN (@MultivalueParam)` without any issues. Just make sure you enclose it with the parenthesis. — B3W, Jul 13 '18 at 17:26

score 0 · Answer 2 · answered Oct 07 '16 at 17:31

0

declare @Vendors_Filter nvarchar(max) = 'a,b,c'
declare @Vendors nvarchar(max) 
set @Vendors =''''+replace(@Vendors_Filter,',',''',''')+'''' 
select @Vendors

answered Oct 07 '16 at 17:31

Voucik

103
3
10

Passing multiple value parameters in SSRS to stored procedure

2 Answers2

Linked