Является ли хорошей практикой использование одной хранимой процедуры, которая принимает переменное количество параметров?

Я работаю над веб-проектом, где мне нужно получить (скажем) записи о сотрудниках. В некоторых случаях мне нужно получить одну запись, указав идентификатор сотрудника. В других случаях мне нужно получить несколько записей о сотрудниках, указав SectorID. Эту логику можно расширить, чтобы охватить дополнительные сценарии: получить все записи о сотрудниках, получить записи о сотрудниках по квалификации и т. д.

Является ли хорошей практикой использование одной хранимой процедуры, которая принимает переменное количество параметров для обработки различных сценариев (используя значения по умолчанию, когда параметр не указан). Бывший:

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)

database sql-server stored-procedures
person Max    schedule 01.10.2009    source источник

Ответы (4)


arrow_upward
2
arrow_downward

вот очень подробная статья на эту тему:

Динамические условия поиска в T-SQL от Erland Sommarskog

он охватывает все проблемы и методы написания запросов с несколькими необязательными условиями поиска.

вот оглавление:

   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
person KM.    schedule 01.10.2009
comment
Статья классная! Бесконечно благодарен! - person Max; 01.10.2009
comment
если вы прочитаете статью, вы увидите, что нет простого и/или правильного ответа. то, как вы справитесь со всем этим, зависит от непосредственной ситуации и компромиссов, на которые вы готовы пойти. - person KM.; 01.10.2009

arrow_upward
0
arrow_downward

Я думаю, что было бы проще поддерживать и тестировать, если бы у вас была одна хранимая процедура с фиксированным интерфейсом для каждого сценария.

person Santiago Cepas    schedule 01.10.2009

arrow_upward
0
arrow_downward

В вашей процедуре есть операторы OR, что не позволяет ей использовать индексы, что приводит к снижению производительности. Вы можете построить SQL динамически (sp_executesql), но это потребует перекомпиляции запроса каждый раз, когда он выполняется. Вы теряете некоторые преимущества хранимых процедур (нет необходимости перекомпилировать запрос каждый раз, когда он используется). Если вы решите это сделать, прочитайте об опции 'WITH RECOMPILE'.

person LukLed    schedule 01.10.2009

arrow_upward
0
arrow_downward

Насколько я знаю, это не рекомендуется. Потому что вам нужно менять свою процедуру каждый раз, когда добавляются новые критерии, и, в конце концов, вы получите массивный критерий, который трудно поддерживать и отлаживать.

Если SP разделен на несколько, тогда другим модулям в вашей системе будет проще получить доступ к своим конкретным потребностям (например, какой-то другой модуль хотел получить emp только по идентификатору сектора, тогда зачем им нужно передавать все необязательные параметры ).

И каждый раз, когда меняется одна и та же SP, каждый, кто потребляет эту SP, должен менять свой DAC.

person RameshVel    schedule 01.10.2009
comment
Меня беспокоит то, что если вы создадите 10 различных хранимых процедур для обработки каждого возможного сценария, их поддержка может стать кошмаром. Если что-то меняется в логике одного sp, то это надо делать и во всех остальных. - person Max; 01.10.2009
comment
если вы используете SQL Server 2005 или более позднюю версию, вы можете использовать IF для нескольких запросов в одной и той же процедуре, и для каждого из них будет сохранен план запроса (эквивалент процедуры для каждой из более старых версий), см. статью в моем ответе или эта ссылка на соответствующий раздел: sommarskog.se/dyn-search-2005.html #ЕСЛИ - person KM.; 01.10.2009
comment
KM, Вы можете сделать это и в более ранних версиях SQL Server. - person HLGEM; 01.10.2009
comment
KM, я просмотрел эту [отличную] статью, но не нашел там ничего, что подтверждало бы ваше заявление, вы можете использовать IF для нескольких запросов в одной и той же процедуре, и для каждого из них будет сохранен план запроса. У вас есть ссылка на это? Я заинтригован и полон надежд. - person Rob Garrison; 01.10.2009