SQL Server 2012 - 11.0.2218.0 Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) I verified onto my SSRS that a multiple-value parameter is managed differently depending on the query type used in the dataset. Report builts using VS10 (monolithic server, ssdb, ssrs, developping, all-in-one) Here is a brief proof of concept: The parameters are defined in the report as: CLICOD, integer, no nulls, allow multiple values FROM_DATE, datetime, no null, no multiple values [the FROM_DATE is just for comparison, CLICOD is the troublesome) The select use the parameters in the where clause, let's say: "WHERE CLICOD IN (@CLICOD) AND ORDDAT=@FROM_DATE" Here is the running code sniffed via Profiler: Dataset query type: Text: exec sp_executesql N'SELECT * FROM myTAB WHERE CLICOD IN (1,2,3) AND ORDDAT=@FROM_DATE',N'@FROM_DATE datetime',@FROM_DATE='2013-02-01 00:00:00' Great. The CLICOD (only) had been directly substituted in the source allowing the multiple value in the IN clause. Stored Proc: exec SSRS_sp_MCInsurers @CLICOD=N'1,2,3',@FROM_DATE='2013-02-01 00:00:00' The CLICOD gets a string! And of course the db engine starts swearing and fires an exception Of course I'd like to go through the sp way... any hints? pf

To handle SSRS multi-valued parameters in stored procs I like to use a string-splitting function. See this post for details. If for some reason (like OCD DBAs), you can go with dynamic SQL in the stored proc. But I like to avoid that whenever possible.BI Developer and lover of data (Blog | Twitter)

I wonder why ms added a built-in feature to manage multi-valued parameters... only for one type of query source! However, since I used copy/paste from Profiler, I did not noted immedately the use of the equal sign: exec SSRS_sp_MCInsurers @CLICOD=N'1,2,3' Then I started thinking (4pm here, it's never too late :)) that in the SP there is no IN clause... because the SP itself uses a table function! Will investigate a bit more (e.g. using table variables) and will get back to you soon p.s. is OCD Obsessive-compulsive disorder? I'm the dba too ;)

indeed, you can use IN but... IN ('1,2,3') is much different than... IN('1','2','3') re: ocd: yes, obsessive-compulsive disorder... not always a bad thing ;-)BI Developer and lover of data (Blog | Twitter)

aye sir. but I'd like to discover which's the switch that makes ssrs acting differently. Just the query type? (the text one parses the parameter correctly). Probably... but the scientific method requires some tests more :)

I have to give up, but now I see the point. How could ssrs pass a multivalued param to an SP? The SP is waiting for one variable of the declared type... No way... other than tabled variables! Which are not allowed afaik.

I believe multi value parameters are passed to a stored procedure as a (N?)VARCHAR(MAX). I agree it is annoying that they are chopped up nice and need for a query source but not for a stored proc source :-/ A table value parameter would be super nice.BI Developer and lover of data (Blog | Twitter)

Hi Pgfiore, In Reporting Services, the data type of the multi-value parameters values are array. However, an array type value cannot be passed to a stored procedure directly. So, we have to use the Join() function in SSRS to convert the parameter values to a string, and use a custom function in the stored procedure to split the array value. For more information, please see: HTTP://BLOG.SUMMITCLOUD.COM/2010/01/MULTIVALUE-PARAMETERS-WITH-STORED-PROCEDURES-IN-SSRS-SQL/ Regards, Mike Yin TechNet Community Support

Hi Pgfiore, In Reporting Services, the data type of the multi-value parameters values are array. However, an array type value cannot be passed to a stored procedure directly. So, we have to use the Join() function in SSRS to convert the parameter values to a string, and use a custom function in the stored procedure to split the array value. For more information, please see: HTTP://BLOG.SUMMITCLOUD.COM/2010/01/MULTIVALUE-PARAMETERS-WITH-STORED-PROCEDURES-IN-SSRS-SQL/ Regards, Mike Yin TechNet Community Support @Mike Sorry, after some investigation with vers 2012 I feel your answer, Mike, is not necessary the best one! At least from a practical point of view. Should also be (and it is) that "multi-value parameters values are array" internally, but: - no, "an array type value cannot be passed to a stored procedure" is wrong. Since 2008 sql server supports it, using table-valued params - No, multi-value parameters values are directly passed as string to stored procedure; without the need of join() function - No, SSRS (neither 2012) is not able to directly refers to table-valued params. This is a SSRS lack of features, not a general characteristic The link you provide is a good reading, but it's based on vers 2005, long ago... This link demostrates how to work around of SSRS limitations: http://geekswithblogs.net/GruffCode/archive/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services.aspx It's a good piece of code, but always a lot of work just for using a table-valued param. I cannot justify Microsoft not to add it to the 2012 version; it's clear, investing in SSRS was not a prior task in the latest 4 years (i.e. 2008,2012 versions)

Hi Pgfiore, In Reporting Services, the data type of the multi-value parameters values are array. However, an array type value cannot be passed to a stored procedure directly. So, we have to use the Join() function in SSRS to convert the parameter values to a string, and use a custom function in the stored procedure to split the array value. For more information, please see: HTTP://BLOG.SUMMITCLOUD.COM/2010/01/MULTIVALUE-PARAMETERS-WITH-STORED-PROCEDURES-IN-SSRS-SQL/ Regards, Mike Yin TechNet Community Support @Mike Sorry, after some investigation with vers 2012 I feel your answer, Mike, is not necessary the best one! At least from a practical point of view. Should also be (and it is) that "multi-value parameters values are array" internally, but: - no, "an array type value cannot be passed to a stored procedure" is wrong. Since 2008 sql server supports it, using table-valued params - No, multi-value parameters values are directly passed as string to stored procedure; without the need of join() function - No, SSRS (neither 2012) is not able to directly refers to table-valued params. This is a SSRS lack of features, not a general characteristic The link you provide is a good reading, but it's based on vers 2005, long ago... This link demostrates how to work around of SSRS limitations: http://geekswithblogs.net/GruffCode/archive/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services.aspx It's a good piece of code, but always a lot of work just for using a table-valued param. I cannot justify Microsoft not to add it to the 2012 version; it's clear, investing in SSRS was not a prior task in the latest 4 years (i.e. 2008,2012 versions)

Not to say there had been a really weird brain behind the use of two different ways for managing a numeric multivalued parameter depending on how many values they are... exec SP1 @iMVP=130 --<== this is numeric and needs an hidden CAST exec SP1 @iMVP=N'130,182' xP