STRING_SPLIT Function

String _Split is a table valued function, which splits the string into multiple substring based on a separator character.

Syntax:

STRING_SPLIT ( string , separator )

·         Output order can be in any order. Final sort order can be overridden by order by clause.

·         Empty string can be filtered out by using where clause. ( Where value <>' ')

Below are few examples to use string_split funcction

Ex:

Select value from string_split('Red,Black,Blue',',')

Output:

value

Red

Black

Blue

Select value from string_split('Red,Black,,Blue',',')

where value<>''

Output:

value

Red

Black

Blue

use AdventureWorksDW2017

Go

SELECT Color,

       EnglishProductName AS ProductName

          from DimProduct

          where Color in (Select value from string_split('Red,Black,,Blue',',')

where value<>'')

Go

Can be used in join

SELECT Color,

       EnglishProductName AS ProductName

          from DimProduct

          join string_split('Red,Black,Blue',',') on value=Color

SSRS : How to pass multi-value Parameter to a Stored Procedure

We will see How to pass multi-value Parameter to a Stored Procedure in report with example.

I have used the example from AdventureWorksDW2017 database.

 Steps For Reports:

Add new report and setup the connection string to AdventureWorksDW2017

Create Report parameter @Color, Check Allow multiple value like below screen shot.

 Now Add new data set to select the All the Colors and map to the Report parameter list so that it can be fetched at run time.

 Modify the Report Parameter Available Value property.

Add new report and setup the connection string to AdventureWorksDW2017

Add new dataset and select the query type as Stored Procure.

Select the store procedure name from drop down list. Here we need to select

Sp_Product.

Navigate to Parameters tab and make sure that Report parameter is selected

Since it is a multi value parameter, in Report parameter tab Click on fx (Expression button) and modify the expression value as below.

 =join(Parameters!Color.Value,",")

After change the expression click OK.

The above parameter value will be passed as comma separated value like (a,b,c)

which can't be used using SQL IN operator. So need to needs to split the comma separated values.

We can do this by using comma separated function.

Either we can write UDFs or can use system function string_split()

Create Stored Procedure. We need to fetch the value based on Product Colors. So Color will be used as Stored procedure parameter.

IF OBJECT_ID ( 'SP_Product', 'P' ) IS NOT NULL 

    DROP PROCEDURE SP_Product;

GO

Create Procedure dbo.SP_Product

@Color as varchar(255)

As

Begin

SELECT Prod.Color,

       Prod.EnglishProductName AS ProductName,

       Fact.SalesAmount,

          Fact.OrderQuantity,

       Fact.TotalProductCost,

       Fact.TaxAmt

      FROM DimProduct AS Prod

         INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey

       Where prod.Color in (Select value from string_split(@Color,',') where RTRIM(value)<>'')

End

Go

Add a table and map all the fields with table fields. Do the formatting well.

Run the report, Select Parameter value from drop down list, then View report.