'SSRS taking a long time to refresh dataset when using a stored procedure as a 'Query Type'
I am using a parameterised stored procedure to retrieve data for a SSRS report. When I create a new shared dataset for the report, it takes a significant amount of time to populate the fields and parameters.
Am I right in thinking that the dataset is attempting to retrieve from the stored procedure in an attempt to populate its fields and parameter definitions?
Is there a way to stop it trying to retrieve my entire database 1.8 million (non parameterised) rows just to populate the fields and parameter definitions?
Solution 1:[1]
The SQL of the stored procedure has been modified to return no records if ALL the parameters are NULL. Whilst this is not ideal it is the only way to work with stored procedures (used in SSRS) for potentially large data retrievals.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | MonkeyMadness |