SQL Server Performance

Using Stored Procedures OUTPUT paramaters with SSRS

Discussion in 'SQL Server 2005 Reporting Services' started by will_uk, Jun 9, 2008.

  1. will_uk New Member

    Hello there
    Iam quite new to SSRS and
    Iam trying to create a simple report to demonstrate the use of the output paramater in a sp and how SSRS will receive this parameter. As an example lets assume we have a sp called USP_Passoutput_ww.
    Use Adventureworks;
    go
    Create Procedure USP_Passoutput_ww
    @Product varchar(40) ,
    @MaxPrice money ,
    @ComparePrice money OUTPUT,
    @ListPrice money OUT
    AS

    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s
    ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice


    -- Populate the output variable @ListPprice.
    SET @ListPrice = (SELECT MAX(p.ListPrice)
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s
    ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);

    -- Populate the output variable @compareprice.
    SET @ComparePrice = @MaxPrice;
    GO
    This is how the above sp would be called in TSQL which iam fine with:
    DECLARE @ComparePrice money,
    @Listprice money

    EXECUTE SP_Passoutput_ww
    '%Bikes%',
    700,
    @ComparePrice OUT,
    @Listprice OUTPUT
    IF @Listprice <= @ComparePrice
    BEGIN
    PRINT 'These products can be purchased for less than
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
    END
    ELSE
    PRINT 'The prices for all products in this category exceed
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
    Iam familiar with the way of working with paramaters in SSRS and i can build the report to let a user send the input parameters (%Bikes%',
    700) to the procedure. When i specify the @ComparePrice OUT,
    @Listprice OUTPUT paramaters in the dataset properties i get an error. But iam unsure as to how SSRS can receive the 2 output parameters back from the proc. Can anyone shed some light on this as 3 of the books and tutorials i have worked through does not mention Output params with Stored Procedures.
  2. satya Moderator

    Reporting Services does not handle output parameters from a SQL Server stored procedure.
  3. dineshasanka Moderator

    You need to get this value as dataset from sp. you cannot use data in output clause

Share This Page