Let's say if we would like to create report with RegionName Parameter that should be able to accept more than one Region value. Our T-SQL will use IN clause and small parenthesis around Parameter Name.
SELECT Col1Name ,Col2Name ,Col3Name FROM dbo.MyTableName WHERE Region IN (@RegionName)
We can also write a Stored Procedure with Multi Value Parameter. To create multi value parameter, We need IN clause and each value in IN Clause should have (single quotes) around. When SSRS pass multiple values to parameter, it separate them by comma ( ,). We need to replace the comma with single quotes and comma as you can see in highlighted code. After that we can write our Dynamic SQL Query to use the value of Parameter.
Create Procedure dbo.sp_TotalSale @Region VARCHAR(500) AS BEGIN Declare @var_Region VARCHAR(500) SET @var_Region=Replace(@Region,',',''',''') --print @var_Region Declare @SQL NVARCHAR(MAX) SET @SQL =' Select [SalePersonFName] ,[SalePersonLName] ,[ProductName] ,[ItemsSold] ,[SoldPrice] ,[SoldDate] ,[City] ,[State] ,[Country] ,[Region] into ##Temp from dbo.TotalSale where Region in ('''+@var_Region+''')' --Print @SQL Execute( @SQL ) Select * From ##Temp drop table ##Temp END Execute dbo.sp_TotalSale 'Asia,Europe'