Passing multivalued parameters to stored procedure

Question

Hi

I,m trying to pass multivalued parameters to a stored procedure in sql server 2008 R2 from SSRS 2008.  I have tried to learn from a book from Teo Lachev where hi explains how to do this with table valued parameters in an Procedure, see also Teo,s article about multivalue parameters  Passing multivalued parameters.  So that part i have learned. But then comes the tricky part for me as almost a beginner. My problem is how to design the report query so it actually works. I,ve managed to use stored procedures in ssrs but not when it comes to multivalued parameters.

My book says that SSRS doesnt support table vaued parameters natively so the suggestion is to add a GetQuery method to the Util Class in the Reporting.Extensibility project.

My question is how to design the report so it works with my code below as easy as possible? And if i need to add a getquery method to the util Class in the reporting.Extensibilit project i wonder if someone has any good example showing how to do this as i never done that before.

Best regards

Arne

USE AdventureWorksDW2008;
GO

CREATE TYPE TerritoryType AS TABLE
(
TerritoryKey int NOT NULL,
TerritoryName nvarchar (50) NOT NULL
)
CREATE PROCEDURE uspGetSalesByTerritory
(
@CalenderYear int,
@Territory TerritoryType READONLY
)
AS 
BEGIN
	SET NOCOUNT ON;
	SELECT ST.SalesTerritoryGroup Territorygroup, D.FullDateAlternateKey Date,
				 SUM(FRS.SalesAmount) ResellerSalesAmount  
	FROM DimDate D
	INNER JOIN FactResellerSales FRS 
	ON D.DateKey = FRS.OrderDateKey 
	INNER JOIN DimSalesTerritory ST
	ON FRS.SalesTerritoryKey = ST.SalesTerritoryKey
	INNER JOIN @Territory T 
	ON ST.SalesTerritoryKey = T.TerritoryKey
	WHERE D.CalendarYear = @CalenderYear
	GROUP BY ST.SalesTerritoryGroup, D.FullDateAlternateKey
END  
 

---

Arne Olsson

Answer 1

hi there 

please follow these steps

1 Create  table value function in your database

CREATE    function [dbo].[SplitString](
 @String varchar (8000),
 @Delimiter varchar (10)
 )
returns @ValueTable table ([Value] varchar(8000))
begin
 declare @NextString varchar(8000)
 declare @Pos int
 declare @NextPos int
 declare @CommaCheck varchar(1)
 if (@String IS NULL) OR (@Delimiter IS NULL) OR (rtrim(@String) = '')
BEGIN
 insert into @ValueTable ( [Value]) Values (@String)
return
END
 
 --Initialize
 set @NextString = ''
 set @CommaCheck = right(@String,1)
 
 --Check for trailing Comma, if not exists, INSERT
 --if (@CommaCheck <> @Delimiter )
 set @String = @String + @Delimiter
 
 --Get position of first Comma
 set @Pos = charindex(@Delimiter,@String)
 set @NextPos = 1
 
 --Loop while there is still a comma in the String of levels
 while (@pos <>  0) 
 begin
  set @NextString = substring(@String,1,@Pos - 1)
 
  insert into @ValueTable ( [Value]) Values (@NextString)
 
  set @String = substring(@String,@pos +1,len(@String))
 
  set @NextPos = @Pos
  set @pos  = charindex(@Delimiter,@String)
 end
 
 return
end

step 2 now in you stored procedure  please use something like this

ALTER  PROCEDURE [dbo].[CPL_REP_LTCCPUnitActivity]
(
 @EntityID AS VARCHAR(MAX)
 
) 
AS

SELECT
 DISTINCT        
      COSTCENTRE.GL_CC_ID1+COSTCENTRE.GL_CC_ID2 as ActivityID,
  COSTCENTRE.GL_CC_ID1+COSTCENTRE.GL_CC_ID2+
   '-'+COSTCENTRE.GL_CC_DESC_ACTIVITY as GL_CC_DESC_ACTIVITY
FROM         
  dbo.GL_COSTCENTRE AS COSTCENTRE
WHERE
 COSTCENTRE.GL_CC_ID1 in
 ( SELECT  value
    FROM
  dbo.SplitString(@EntityID,','))

i am assuming @EntityID is your multi valued parameter

i hope this will help

many thanks

syed qazafi anjum

please mark as helpful if you find it helpful

Answer 2

I don't think it answers the original question of passing TVP to stored procedure from the report.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog