I did some research on the internet into which source is better for a Reporting Services report. Below are the advantages of using Stored Procedures.
- The query is stored separately, outside SSRS. You can now change the query without opening SSRS. Logic stays in the database. When something changes, you can also analyse more quickly what impact a change has on, for example, columns (after all, the column names are in the stored procedure).
- The execution plans that SQL Server keeps are stored when you use stored procedures. With T-SQL statements fired from an SSRS report this is not the case
- Some things are not possible in a T-SQL query in SSRS, such as using temp tables, table variables and indexing temp tables
- Stored procedures form an abstraction layer between the reports and the business logic
- Stored procedures make it possible to reuse logic.
In short, use Stored Procedures as the source for your report. Teach yourself to use naming conventions for these procedures, for example spcRptRapportOverVerzuim. When the number of reports and procedures grows, they are listed neatly by category.
Frequently asked questions
Why use stored procedures as the source for an SSRS report?
The query lives outside SSRS, so you can change it without opening the report. The logic stays in the database and you can reuse it. The execution plans are also kept.
Can I use temp tables in a T-SQL query in SSRS?
No. Temp tables, table variables and indexing temp tables are not possible in a T-SQL query in SSRS. In a stored procedure they are.
Are execution plans kept for an SSRS report?
With stored procedures, yes. With T-SQL statements fired from an SSRS report, they are not.
How should I name stored procedures for SSRS reports?
Use a fixed naming convention, for example spcRptRapportOverVerzuim. When the number of reports and procedures grows, they are then listed neatly by category.
How do I see the impact of a column change on SSRS reports?
The column names are in the stored procedure. That way you can analyse the impact of a change faster. The procedure also forms an abstraction layer between the reports and the business logic.
Comments
Related blogs
Tablix filter with the LIKE operator
Exporting more than 65,000 rows to Excel
How do I back up my SSRS report as RDL