Как снова использовать код в хранимых процедурах SQL?

«Лучшим» способом для этого конкретного сценария было бы использование какого-либо типа генерации кода. Придумайте какое-то соглашение и включите его в генератор кода.

Пробовали ли вы добавить более одного типа параметра запроса в список параметров для вашей основной процедуры? Я написал процедуру только для покрытия таблицы Invoice, вам нужно будет расширить ее для дополнительных таблиц.

CREATE PROCEDURE hd_invoice_select
(
    @id INT = NULL
    , @customerId INT = NULL
) AS
BEGIN
    SELECT * 
        FROM Invoice 
        WHERE 
            (
                @id IS NULL
                OR InvoiceID = @id
            )
            AND (
                @customerId IS NULL
                OR CustomerID = @customerId
            )
    RETURN
END

Эту процедуру можно назвать широко открытой, отправив @id и @customerId как NULL для конкретного InvoiceID на основе @id с @customerId равным NULL (или просто оставьте его все вместе), или для конкретного клиента на основе @customerId, оставив @id равным NULL, или исключите его из запроса.

Вам также следует посмотреть на представления и таблично-значения пользователя -Определенные функции. Вы можете поместить их в свои процессы, чтобы убрать часть логики от процедур, чтобы их можно было совместно использовать и поддерживать в одном месте. Наличие некоторой логики в представлениях / функциях также позволяет вам работать с данными в окне запроса, как если бы это была таблица.

I'm the person who asked this question in the first place. I'm answering my own question here to let you know the code reuse solution I use and to get your comments on that approach. If this answer gets a lot of up votes, I will select it as the final answer.

This approach works and is simple to use. I don’t know if it has a performance impact because it relies heavily on temporary tables.

For each concept in my application, I have one storec proc like this:

CREATE PROCEDURE hd_invoice_selectFromTempTable AS

    /* Get the IDs from an existing #TempInvoiceIDs temporary table */

    SELECT * FROM Invoice WHERE InvoiceID IN
        (SELECT ID FROM #TempInvoiceIDs)

    SELECT * FROM InvoiceItem WHERE InvoiceID IN 
        (SELECT ID FROM #TempInvoiceIDs)

    SELECT * FROM InvoiceComments WHERE InvoiceID IN
        (SELECT ID FROM #TempInvoiceIDs)

    RETURN

Then I create as many selection stored proc as I need:

CREATE PROCEDURE hd_invoice_select(@id INT) AS

    /* Fill #TempInvoiceIDs with matching IDs */
    SELECT id AS ID INTO #TempInvoiceIDs

    EXEC hd_invoice_selectFromTempTable
    RETURN

CREATE PROCEDURE hd_invoice_selectAllForCustomer(@customerID INT) AS

    /* Fill #TempInvoiceIDs with matching IDs */
    SELECT invoiceID as ID
    INTO #TempInvoiceIDs
    FROM Invoice WHERE CustomerID = @customerID

    EXEC hd_invoice_selectFromTempTable
    RETURN

CREATE PROCEDURE hd_invoice_selectAllActive AS

    /* Fill #TempInvoiceIDs with matching IDs */
    SELECT invoiceID as ID
    INTO #TempInvoiceIDs
    FROM Invoice WHERE Status = 10002

    EXEC hd_invoice_selectFromTempTable
    RETURN

What do you think of this approach? It is somewhat similar to AlexKuznetsov's answer but I use temp tables instead of a BLOB parameter.

Это одна из основных проблем с хранимыми процедурами и почему они не нравятся людям.

Я никогда не находил и не видел способа обойти это.

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

У меня есть предложение, не связанное с вашим вопросом - вместо использования предложения IN используйте предложение EXISTS в ваших операторах SQL.

I ' Мы унаследовали приложение, которое раньше использовало подход с временными таблицами, и я согласен, что это очень беспорядочно.

В этом проекте мы смогли удалить множество временных таблиц, заменив их представлениями, которые содержали нужные нам «объекты», а затем обновили наши хранимые процедуры, чтобы запросить эти представления.

Возможно, это сработает и в вашей ситуации.

В некоторых случаях я использую ПРОСМОТРЫ для повторного использования «кода». В таких случаях, как фильтры, активные элементы, устаревшие элементы и т. Д ...

Возможно, вам стоит научиться использовать объединения. Вы можете поместить базовое объединение трех таблиц в представление и просто запросить его с помощью sp, передающего различные параметры. Кроме того, вам не следует вообще использовать select * в производственном коде. Верните только те несколько столбцов, которые вам действительно нужны в данных обстоятельствах, и вся ваша система будет работать лучше. К тому же у вас не будет непредвиденных результатов, когда люди изменят вашу структуру.

Иногда я делаю это в два этапа:

Я придумываю список InvoiceID. Затем я вызываю свою хранимую процедуру с этим списком в качестве параметра.

В 2005 году у нас нет параметров с табличным значением, поэтому я упаковываю свой список идентификаторов в двоичный большой двоичный объект и отправляю его на SQL Server, как описано здесь: Массивы и списки в SQL Server 2005

Вы также можете отправить список идентификаторов в виде списка, разделенного запятыми (несколько медленно) или в виде конкатенации строковых представлений фиксированной ширины (намного быстрее).