I have a SSRS project in which multiple reports are using the same stored procedure. All reports are different (obviously) but the data source is same.
For example one report is a larger tabular transaction report with 50+ fields and 20+ parameters, one report is a one parameter and one page report essentially just showing one record in a detailed view. There are few other reports which are very similar to the first (tabular) report but most of the parameters are defaulted and hidden and they use different fields from the stored procedure.
All of these reports are using same stored procedure (say
sp_Transaction) which has 20+ parameters and returns 100+ fields. The way the arguments are handled in the stored procedure is something like this:
( ( tblTransaction.TransactionID = @TransactionId ) OR ( @TransactionId IS NULL AND ( @DateCreatedDaysRelative IS NULL OR (CAST(tblTransaction.Created AS DATE)) = CAST(DATEADD(DAY, @DateCreatedDaysRelative, GETDATE() ) AS DATE) ) AND ( ( @DateStartMonthsRelative IS NULL AND @DateEndMonthsRelative IS NULL ) OR ( tblTransaction.DateStart <= DATEADD(MONTH, @DateStartMonthsRelative, GETDATE() ) AND ( tblTransaction.DateEnd >= DATEADD(MONTH, @DateEndMonthsRelative, GETDATE() )) ) ) ) ) AND ( tblTransaction.Receipt = @ReceiptRequired OR @ReceiptRequired IS NULL ) AND ( @PubID = dbo.tblJournal.PubID OR @PubID IS NULL )
I understand its easy to maintain having one code for all report but it's a big stored procedure which takes a lot of arguments and returns a lot of fields and non of the report (that uses it) actually uses all its arguments or fields.
So my question is 'is it a good practice to have one common big piece of code where half the inputs and outputs are not in use (at a time by one report) or creating separate stored procedures for each report with only required input and outputs; or something else like one stored procedure but with dynamic sql?
This is not a simple Yes/No question.
You have to consider a lot of elements, and like a lot of other things, you should not overuse it.
I would say that bad ideas are:
- Always use a separate query / SP
- Always use the same query / SP
Where to place the cursor (mostly creating new queries or mostly capitalizing on the same query) between these 2 options is up to you.
Both have pros and cons, which, on the top of my head, are:
Separate query / SP
- PRO: Tailored queries for each report that are just processing "useful" data
- CON: If an update is needed (like a column name change, or a new column needed for example), you have to update x queries instead of only 1
Same query / SP
- PRO: An update can affect all the reports, so you have less work
- CON: An update can affect all the reports, so you can have side effects