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.
