none
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

    Tuesday, April 24, 2012 6:34 PM

Answers

  • 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

    • Marked as answer by Arne Olsson Wednesday, April 25, 2012 5:45 PM
    Wednesday, April 25, 2012 3:08 AM

All replies

  • 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


    Wednesday, April 25, 2012 12:29 AM
  • 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

    • Marked as answer by Arne Olsson Wednesday, April 25, 2012 5:45 PM
    Wednesday, April 25, 2012 3:08 AM