Multiple reports in SSRS using same stored procedure

Question

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 smells like a maintenance nightmare. How will you remember which segment of this proc is tied to which report? If you're asking the question, it sounds like you're already suspecting it's not a great idea. — InbetweenWeekends
– InbetweenWeekends, Commented Jun 23, 2015 at 16:00
It might be a little easier to mess up the parameter mapping in SSRS when there are a lot of a parameters and/or the names aren't clear. Perhaps you just need to isolate some of the more intricate checks to their own procedures or functions so they can be shared. — shawnt00
– shawnt00, Commented Jun 23, 2015 at 16:41
I totally agree with @onskee! If you are forced by other factors to maintain a single piece of script, just remember that the WITH RECOMPILE option may increase the performances: msdn.microsoft.com/en-us/library/ms190439.aspx — lucazav
– lucazav, Commented Jun 23, 2015 at 17:05

Sébastien Sevrin · Accepted Answer · 2015-06-23 16:18:00Z

2

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

answered Jun 23, 2015 at 16:18

Sébastien Sevrin

5,4132 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KMan Over a year ago

Agree mostly, I understand using same SP has a PRO of less work on update but also has a CON (in addition to what you said above) : you do unnecessary processing/iterations every time being the generic query. Just wanted to know what community thinks.

Sébastien Sevrin Over a year ago

Just to be clear, I'm not saying that having 1 master SP is something that you should do. There are times where a few reports could be similar enough to share a SP and different enough not to use a shared dataset, that's the time where you want to use the same SP for 2 reports. If you don't know which choice to make, then go for a different query.

Trubs · Accepted Answer · 2015-06-23 22:47:31Z

This is a very simple yes/no problem. This is a terrible, terrible idea.

Maintenance is a significant issue that you dont want to deal with, testing every report with all parameters after any change would be an nightmare and a great way to introduce bugs.

An equally, if not more significant issue is performance. This proc won't scale in any way. It wont be able to use indexes as there are functions in the where clause that need to be evaluated for every row. (there are ways to mitigate this issue, but this would only make code even less readable)

Is the report is run often, does it recompile for each execution? The execution plan is unlikely to be efficient for all parameters but if the plan is cached, calling the proc with different params will effect the run time in unknown ways. (again, there are ways to mitigate this, but code readablity would suffer)

There are definitely reasons to use shared datasets and re-use stored procs, but having an uber stored proc for all reports is a bad idea.

Collectives™ on Stack Overflow

Multiple reports in SSRS using same stored procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related