Using a stored proc parameter in an SSRS report

Transact-SQL
1

I have a stored proc that writes its final results to a temp table. From this table I need to deliver (1) all results or (2) top 50 results by measure x, using a parameter.
I have created @ShowTop50 and defaulted to 'N'. The code after the final temp table I have done as follows -
IF @ShowTop50 = 'Y'
BEGIN
SELECT TOP50 * from [#table]
ORDER BY ABS([Measure x]) DESC
END
ELSE
BEGIN
SELECT * FROM [#table]
END
This works perfectly when run in the procedure itself (selecting 'Y', 'N' or nothing to default to 'N') but I can't see how to use this paremeter/functionality in an SSRS report. (not too familiar with SSRS).
Could anyone help?
Thanks!

2

I would not recommand you to use any visual of a report to have impact on your stored procedure. Keep the logic in the database and the report things on the report.

So @ShowTop50 should never ever be part of your stored procedure. When you deliver the report to your boss and he ask you to show 2 extra results to the report because it fits on the paper, do you want to change your parameter to @ShowTop52? And inform your colleagues who uses it also?

Having said this, you can find the answer here:
Using a Temporary Table in SSRS – SQLServerCentral Forums

3

Thankyou for this. Re Top50 it is a fixed requirement. If they need more they can run with the parameter set to ‘N’. My query wasn’t about creating temp tables but about getting the Y/N parameter to work in SSRS. Any help appreciated.

4

I would use the t-sql row_number function and in the where statement something like this:

WHERE CASE WHEN @ShowTop50='Y' THEN RowNumber <=50 END

ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn

Different where clause based on SSRS parameter - Database Administrators Stack Exchange