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

Question

I am working on a web project where I need to get (let them say) employee records. In some cases, I have to get one record by providing EmployeeID. In other cases, I should get a few employee records by providing the SectorID. This logic can be expanded to cover additional scenarios: get all employee records, get employee records for qualifications, etc.

Is it good to use a single stored procedure that accepts a variable number of parameters for processing different scripts (using default values ​​if the parameter is not specified). Example:

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

here is a very complete article on this topic:

Dynamic T-SQL Search Terms by Erland Sommarskog

it covers all the problems and methods of trying to write queries with several optional search terms

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 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 

Answer 2

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

Answer 3

He was not recommended, as I know. Since you have to change your procedure every time new criteria are added, and finally you get a massive one that is difficult to maintain and debug.

If the SP is divided into several, then it will be easier for other modules of your system to access their specific needs (for example, some other module would like to get emp by a separate sector identifier, why should they pass all the optional parameters).

And every time the same SP changes, everyone consuming this sp must change its DAC.

Answer 4

Your procedure has an OR, which prevents the use of indexes, which results in slow performance. You could build SQL dynamically (sp_executesql), but you would need to recompile the query every time it is executed. You lose the advantage that stored procedures have (there is no need to recompile the request each time it is used). If you decide to do this, read the "WITH RECOMPILE" option.