Is it good practice to use one single stored procedure that accepts a variable number of parameters

Question

I am working on a web project where I have to retrieve (let's say) employee records. In some cases I have to retrieve a single record by providing an EmployeeID. In other cases, I have to retrieve multiple employee records by providing a SectorID. This logic could be expanded to cover additional scenarios: get all employee records, get employee records by qualification, etc.

Is it good practice to use one single stored procedure that accepts a variable number of parameters to handle different scenarios (using default values when the parameter is not provided). Ex:

CREATE PROCEDURE [dbo].[GetEmployeeRecords]
(
    @employeeID int = -1,
    @sectorID int = -1
)

AS

BEGIN

    SELECT EmployeeID,
      EmployeeFirstName,
      EmployeeLastName,
      s.SectorName

    FROM dbo.Employees e

    INNER JOIN Sectors s ON e.SectorID = s.SectorID

    WHERE (e.EmployeeID = @EmployeeID OR @EmployeeID = -1)

    AND (e.SectorID = @SectorID OR @SectorID = -1)

Answer 1

I think it would be easier to maintain and test is you have one stored procedure with a fixed interface for each scenario.

Answer 2

Its not recommened as i know. Because you have to change your procedure everytime the new criteria is added and finally you would get a massive one which is hard to maintain and debug.

If the SP is split into multiple, then it would be easier for other modules in your system to access their specific needs (like some other module wanted to get the emp by sector id alone, then why do they need to pass all the optional paramters).

And everytime the same SP changes, every one consuming this sp has to change their DAC.

Cheers

Ramesh vel

Answer 3

Your procedure has ORs, which prevents it from using indexes, which ends with slower performance. You could build SQL dynamically (sp_executesql), but it will require to recompile query every time it is being executed. You lose some advantaged that stored procedures have (no need to recompile query every time it is used). If you decide to do it, read about 'WITH RECOMPILE' option.

Answer 4

here is a very comprehensive article on this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

it covers all the issues and methods of trying to write queries with multiple optional search conditions

here is the table of contents:

   Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History