I think sometimes those of us that have been doing database administration/development for a while take it for granted that everyone knows the basics. One such basic is parameterizing stored procedures. This allows us to potentially consolidate multiple stored procedures into a single procedure.  It’s as simple thing to do that many don’t.

I try to parameterize as many stored procedures as possible. This not only minimizes the amount of procedures I need to maintain, it in my opinion is a much cleaner way to code. It disturbs me when I see multiple stored procedures that pull the exact same data, but may have slight differences between them. Whether it be a sort, a where clause, or even just an extra field or two that makes it different, some developers think you need a different procedure for each one . Why not consolidate and parameterize?

Exhibit A

The code below is an example of a real work scenario.  Originally, it was 8 stored procedures and with 8 correlated reports. By simply adding a Report Type parameter I was able to make it one stored procedure and as well as consolidate to a single report.

CREATE PROC [dbo].[rptTrackMonthlyStats]
    @ReportType CHAR(2) ,
    @year INT ,
    @StartDate SMALLDATETIME ,
    @EndDate SMALLDATETIME
AS /**********************************************************************************************
Name: [rptTrackMonthlyStats]
Programmer: MRathbun
Date Created: 5/13/2015
Description: Reports all  activity for given period
Called By: report Monthly Stats
Parameters: @ReportType CHAR(2),
            @year int,
            @startDate smalldatetime,
            @endaDate smalldatetime
Run: [rptTrackMonthlyStats] 'C','2015',null,null
 [rptTrackMonthlyStats] 'R','2015'
 [rptTrackMonthlyStats] 'T','2015'
 [rptTrackMonthlyStats] 'TD','2015','10/01/2015','10/31/2015'
 [rptTrackMonthlyStats] 'DC','2015','10/01/2015','10/31/2015'
 [rptTrackMonthlyStats] 'TT','2015','1/01/2015','12/31/2015'
 [rptTrackMonthlyStats] 'C','2015','10/01/2015','10/31/2015'
Modifications:
Date: Ticket Programmer: Desc:
***********************************************************************************************/    SET NOCOUNT ON;
    IF @ReportType = 'C'--Closed Rolling 18
        BEGIN
             SELECT  FiscalMonthName ,
                    FiscalMonth ,
                    FiscalYear ,
                    COUNT(*) AS TotalClosed ,
                    0 AS TotalOpen ,
                    'C' AS OpenClosed
            FROM    dbo.tasks c
                    JOIN DimTime ON CAST(CLSDDATE AS DATE) = ActualDate
            WHERE   CLSDDATE >= DATEADD(m, -24, GETDATE())
                    AND ( (FiscalMonth + FiscalYear <> DATEPART(m, GETDATE())
                          + DATEPART(yy, GETDATE())) )
            GROUP BY FiscalMonth ,
                    FiscalYear ,
                    FiscalMonthName
        END;
    IF @ReportType = 'TT'--Closed 12 months
        BEGIN
             SELECT  CASE WHEN [type] = 'Job Failure' THEN 'Bug'
                         WHEN [type] LIKE 'Data%' THEN 'Move/Add/Change'
                         WHEN [type] IS NULL THEN 'No Type'
                         WHEN [type] LIKE 'New Report%'
                              OR [TYPE] LIKE 'Change Report or System'
                         THEN 'New/Change Report or System'
                         ELSE [type]
                    END AS type ,
                    FiscalMonthName ,
                    FiscalYear ,
                    COUNT(*) AS Total ,
                    FiscalMonth
            FROM    tasks
                    JOIN DimTime ON CAST(CLSDDATE AS DATE) = ActualDate
            WHERE   DATEDIFF(MONTH, CLSDDATE, GETDATE()) <= 12
                    AND CLSDDATE <= GETDATE()
            GROUP BY [type] ,
                    FiscalMonthName ,
                    FiscalYear ,
                    FiscalMonth
            ORDER BY COUNT(*) DESC;
         END;
    IF @ReportType = 'R'--Completed by Year
        BEGIN
             SELECT  DEPT ,
                    COUNT(*) AS Total
            FROM    tasks
            WHERE   DATEPART(YEAR, CLSDDATE) = @year
                    AND DEPT IS NOT NULL
            GROUP BY dept;
         END;
    IF @ReportType = 'T'--Closed by Technician
        BEGIN
             SELECT  CLSDBY ,
                    COUNT(*) AS Total
            FROM    tasks
            WHERE   DATEPART(YEAR, CLSDDATE) = @year
            GROUP BY CLSDBY;
         END;
     IF @ReportType = 'TD'--Closed by Technician
        BEGIN
             SELECT  CLSDBY ,
                    COUNT(*) AS Total
            FROM    tasks
            WHERE   CLSDDATE BETWEEN @StartDate AND @EndDate
            GROUP BY CLSDBY;
         END;
    IF @ReportType = 'DR'--Requested by Department
        BEGIN
             SELECT  CASE WHEN DEPT = 'RET/NA/CB/Sales' THEN 'Sales'
                         WHEN DEPT = 'Sales Planning and Analysis'
                         THEN 'Planning'
                         WHEN DEPT = 'Human Resources' THEN 'HR'
                         WHEN DEPT = 'Maintenance' THEN 'Manufacturing'
                         ELSE DEPT
                    END AS DEPT ,
                    COUNT(*) AS Total
            FROM    tasks
            WHERE   REQDATE BETWEEN @StartDate AND @EndDate
                    AND DEPT <> 'IT'
            GROUP BY DEPT;
         END;
    IF @ReportType = 'DO'--Requested by Department
        BEGIN
             SELECT  CASE WHEN DEPT = 'RET/NA/CB/Sales' THEN 'Sales'
                         WHEN DEPT = 'Sales Planning and Analysis'
                         THEN 'Planning'
                         WHEN DEPT = 'Human Resources' THEN 'HR'
                         WHEN DEPT = 'Maintenance' THEN 'Manufacturing'
                         ELSE DEPT
                    END AS DEPT ,
                    COUNT(*) AS Total
            FROM    tasks
            WHERE   REQDATE BETWEEN @StartDate AND @EndDate
                    AND CLSDDATE IS NULL
                    AND DEPT <> 'IT'
            GROUP BY DEPT;
         END;
    IF @ReportType = 'DC'--Requested by Department
        BEGIN
             SELECT  CASE WHEN DEPT = 'RET/NA/CB/Sales' THEN 'Sales'
                         WHEN DEPT = 'Sales Planning and Analysis'
                         THEN 'Planning'
                         WHEN DEPT = 'Human Resources' THEN 'HR'
                         WHEN DEPT = 'Maintenance' THEN 'Manufacturing'
                         ELSE DEPT
                    END AS DEPT ,
                    COUNT(*) AS Total
            FROM    tasks
            WHERE   CLSDDATE BETWEEN @StartDate AND @EndDate
                    AND CLSDDATE IS NOT NULL
                    AND DEPT <> 'IT'
            GROUP BY DEPT;
         END;

To add a new dataset just right click on Datasets and choose Add Dataset. Since the report is a stored procedure we set the dataset connection string to the stored procedure name and its parameters. This is just my preferred method. You can also choose the stored procedure from the drop down.

rptTrackMonthlyStats @ReportType, @year, @startdate, @enddate

In the Report Type parameter, choose add Available Values. I typed in each option so the user could choose which report layout/data they wanted to see from drop down. That parameter will be passed to the stored procedure upon execution and the proper dataset will be returned. The users will never see the T, TD etc. they only see the label so it doesn’t make any difference to them what those are.

You can even go as far as using these parameters to hide and show different report elements, but that’s for another time. Stay tuned for more back to the basics.

NOTE: There are some reasons not to do this, like the reuse of the execution plans and parameter sniffing but in these cases consolidating would not be an issue as they use the same parameters.