0845 643 64 63

Use Stored Procedure Output Parameters in SSRS

It’s commonly known that there are a few frustrations when using stored procedures as the source for SSRS 2005 datasets, the inability to cope with temporary tables is always a key one, another is not being able to get at output parameters or return values.

This post explains the easiest way to get at the output parameters and return value.

Lets assume you are using the following stored proc:


IF OBJECT_ID('myProc') IS NOT NULL
  DROP PROCEDURE myProc
GO
CREATE PROCEDURE dbo.myProc
  @OutTest int OUTPUT
AS
  SET @OutTest = 42

  --Random test query
  SELECT 1 as x, 'rec1' as y, 'rec1' as z
  UNION
  SELECT 2, 'rec2', 'rec2'

  RETURN 1
GO

When we set the dataset to use myProc, we will get the three fields (x, y and z) returned, but not the @OutTest parameter, or the return value of 1.

To fix this all you need to do is create a wrapper procedure that converts the values into extra fields.


IF OBJECT_ID('myProcWrapper') IS NOT NULL
  DROP PROCEDURE myProcWrapper
GO
CREATE PROCEDURE dbo.myProcWrapper
AS

  CREATE TABLE #myTmp(
    x int,
    y varchar(20),
    z varchar(20)
  )

  DECLARE @ReturnValue int, @OutTest int

  INSERT INTO #myTmp
    EXEC @ReturnValue=dbo.myProc @OutTest OUTPUT

  SELECT x, y, z,
      @ReturnValue as RetVal,
      @OutTest as [Output]
    FROM #myTmp

  Drop Table #myTmp

  RETURN 1
GO

This runs the original procedure, and stores the values in a temporary table (yes, I know I know…!). It then appends the return value and output parameters as extra fields and then returns the whole lot. These values are repeated for every record returned, but all you have to do is take the value from the first record =First(Fields!Output.Value).

If you wanted to get rid of the temporary table, you could always replace it with a permenant table, but you’d have to be careful to keep each client instance seperate so results for different users didn’t interfere with each other. You could do this by adding a working column to the myTmp table contaning the username and/or workstation ID. Something like this…


IF OBJECT_ID('myTmp') IS NOT NULL
  DROP TABLE myTmp
GO

CREATE TABLE myTmp(
  x int,
  y varchar(20),
  z varchar(20),
  UserID varchar(50),
  Workstation varchar(50)
)
GO
ALTER TABLE dbo.myTmp ADD CONSTRAINT
  DF_myTmp_UserID DEFAULT SYSTEM_USER FOR UserID
GO
ALTER TABLE dbo.myTmp ADD CONSTRAINT
  DF_myTmp_Workstation DEFAULT Host_Name() FOR Workstation
GO

IF OBJECT_ID('myProcWrapper') IS NOT NULL
  DROP PROCEDURE myProcWrapper
GO
CREATE PROCEDURE dbo.myProcWrapper
AS

  DECLARE @ReturnValue int, @OutTest int

  DELETE FROM myTmp
    WHERE UserID = SYSTEM_USER
    AND Workstation = Host_Name()

  INSERT INTO myTmp (x,y,z)
    EXEC @ReturnValue=dbo.myProc @OutTest OUTPUT

  SELECT x, y, z,
      @ReturnValue as RetVal,
      @OutTest as [Output]
    FROM myTmp
    WHERE UserID = SYSTEM_USER
    AND Workstation = Host_Name()

  DELETE FROM myTmp
    WHERE UserID = SYSTEM_USER
    AND Workstation = Host_Name()

  RETURN 1
GO

This still has the benefit of not having to modify the original stored procedure, but now allows you to see the available fields in Reporting Services.

3 Responses to Use Stored Procedure Output Parameters in SSRS

  • Judging by the date of you post, you must be using SQL 2005 with SSRS 2005.

    Not that I don’t appreciate the gymnastics you have performed, but if your OUTPUT value can be derived from a sub-query, e.g. rowcount or applying an aggregate function, e.g. SUM() on your data, you could leverage something like:

    CAST(COUNT(RecordID) OVER (PARTITION BY 1) AS VARCHAR(9)) AS [RecordCount] <– Partition by 1 to get a total number of records count value, which you could also have done with @@RowCount via OUTPUT param, which of course you can’t use in SSRS.

    Then you use the =First(Fields!Output.Value) as you suggested in the report. At least you don’t have to deal with temp tables, all that IO to tempdb and the administration of cleaning up afterwards.

    Hope this is of some use!

  • Thanks for your comment Drikus, and that’s a very nice way of getting the info out.

    The problem in most situations is that the return value from stored procs are status flags (did the proc work?, what state is the base data in, what kind of data is being returned etc. etc.), which is additional data, not derived from the dataset. Even in this situation though, the best solution is to edit the stored proc and append the value to the dataset as you’ve pointed out. However in many situations the poor old report developer is not allowed to edit the source stored proc, so must find a workaround.

    Like any workaround, there is always a hit somewhere, and you’re right that temporary tables are best avoided if at all possible, not least as they are not supported very well in SSRS!.

    And yes, I was using SQL 2005 – I should really make that clear in posts – point noted thanks!

  • This is what I’ve been looking for! Thank you so much for sharing this!

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon