Can “allow multiple values” for parameters be used

Question

Microsoft says that you cannot allow multiple values for a parameter in Report Builder 2.0 when using stored procedures. https://technet.microsoft.com/en-us/library/Dd207127(v=SQL.100).aspx

I cannot find a similar statement for Report Builder 3.0. How does report builder send the data contained in a parameter with multiple selections to a stored procedure? It seems from this post that it passes them as a string, is that right? https://www.mssqltips.com/sqlservertip/2844/working-with-multiselect-parameters-for-ssrs-reports/

This seems way too complicated of a method to unparse the string sent by Report Builder to the database.

It seems that tables can be passed as parameters into stored procedure in SQL Server 2008: How to pass an array into a SQL Server stored procedure Is there a way to pass a table from Report Builder 3.0 to SQL Server 2008?

Answer 1

Yes it can be used. The easiest way I have found to do it is, stucture you sproc, such that the variable is VARCHAR(MAX) and the where clause uses an inclause. Then use another sproc to break up the comma delimited variable that will be passed.

Example 
@States = 'CA,NM,NY'

Select *
from Table 
Where State in (Select CSVResults from parsecsv(@States,',')

This assumes of course you have a function called Parsecsv. If you need one I use.

CREATE FUNCTION [dbo].[ParseCSV] (@CSV_STR VARCHAR(8000),@Delimiter varchar(20) )
 RETURNS @splittable TABLE (ID int identity(1,1), CSVvalues VARCHAR(256) )
AS
BEGIN  

-- Check for NULL string or empty sting
    IF  (LEN(@CSV_STR) < 1 OR @CSV_STR IS NULL)
    BEGIN
        RETURN
    END

    ; WITH csvtbl(i,j)
    AS
    (
        SELECT i=1, j= CHARINDEX(@Delimiter,@CSV_STR+@Delimiter) 

        UNION ALL 

        SELECT i=j+1, j=CHARINDEX(@Delimiter,@CSV_STR+@Delimiter,j+1)
        FROM csvtbl
        WHERE CHARINDEX(@Delimiter,@CSV_STR+@Delimiter,j+1) <> 0
    )   

    INSERT  INTO @splittable  ( CSVvalues)
    SELECT  LTRIM(RTRIM(SUBSTRING(@CSV_STR,i,j-i)))
    FROM    csvtbl OPTION (MAXRECURSION 32767)

    RETURN
END