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.
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!