SSRS Tutorial 37 - Use Stored Procedure with Multi value Parameter in SSRS Report

In this video of SSRS Tutorial, You will be learning

  1. How to create Stored Procedure with Multi value parameter
  2. How to configure Multi value parameter in SSRS Report
  3. How to provide values to Parameter by using Query

ALTER 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'
 


Use Stored Procedure with Multi value Parameter in SSRS Report - SSRS Tutorial

         
          

2 comments:

  1. Good evening. Why do we need to create a function or use dymanic SQL for multi valued params in USP but we don't need it if we're writing a query instead of using USP?
    Thanks

    ReplyDelete
  2. I don't understand this Procedure if You can explain me??

    ReplyDelete