Monthly Archives: August 2008
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.