Show parameters from SSRS to Stored Procedure

Question

I have a stored procedure that is called from an SSRS report. The stored procedure on its own works fine (for example, code below returns expected results). The report passes in 2 parameters, both DateTime, but these are seemingly being completely ignored. How can I verify the values being passed into the stored procedure from within the stored procedure?

USE [MyDatabase]
GO
DECLARE @return_value int
EXEC    @return_value = [reports].[uspMyReport]
        @StartDate = N'3/15/2013',
        @EndDate = N'3/16/2013'

SELECT  'Return Value' = @return_value
GO

From with BIDS, within a shared dataset, the sproc is called like so:

EXEC reports.[uspMyReport] @StartDate, @EndDate

Answer: I can see the value that has been passed into the stored procedure like so:

DECLARE @StartDateString VARCHAR(50)
SELECT @StartDateString = CAST(@StartDate AS VARCHAR)
RAISERROR(N'StartDate: %s', 18, 0, @StartDateString)
RETURN

Posting the sproc's code isn't really possible, nor will it help. The above code calls the sproc with valid parameters and the sproc works correctly. So, I think the report is passing in invalid parameters (though they look good on the SSRS side) and I would like to see what they are when inside the sproc. — Al Lelopath
Please add the SQL command from the SSRS report so it is clear how you call the procedure. — H B

H B H B · Accepted Answer · 2013-12-06T21:00:23

There are a few ways to check how far the parameter values get:

You could raise an error as described here: The "right" way to do stored procedure parameter validation This verifies if the values reach the stored procedure: If they don't, the storedproc parameters probably aren't linked to the Report parameters and you need to configure the report. If they do, the final select query somehow doesn't use them and you need to fix the storedproc.

Another way: use the same check not to raise an error but to return a dummy dataset maybe containing error information which will then show up in the report (probably only useful for testing scenarios).

And finally you could start up SQL Server profiler just before running a report and see how exactly your procedure is called from SSRS. This way you don't have to change the procedure to debug the call itself.

Show parameters from SSRS to Stored Procedure

1 Answers