使用一个接受可变数量参数的单个存储过程是否是一种好习惯？ [英] Is it good practice to use one single stored procedure that accepts a variable number of parameters

问题描述

我正在一个网络项目中，我必须检索（比如说）员工记录。在某些情况下，我必须通过提供EmployeeID来检索一条记录。在其他情况下，我必须通过提供SectorID来检索多个员工记录。此逻辑可以扩展为涵盖其他方案：获取所有员工记录，按资格获取员工记录等。

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)

推荐答案

有关此主题的文章非常全面：

here is a very comprehensive article on this topic:

T-SQL中的动态搜索条件，作者：Erland Sommarskog

它涵盖了尝试编写具有多个可选搜索条件的查询的所有问题和方法

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

目录：

   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

这篇关于使用一个接受可变数量参数的单个存储过程是否是一种好习惯？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！