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.