如何重用SQL存储过程中的代码？

Question

我们使用SQL Server 2005。我们所有的数据访问都是通过存储过程完成的。 我们的选择存储过程总是返回多个结果集。

例如：

CREATE PROCEDURE hd_invoice_select(@id INT) AS
    SELECT * FROM Invoice WHERE InvoiceID = @id
    SELECT * FROM InvoiceItem WHERE InvoiceID = @id
    SELECT * FROM InvoiceComments WHERE InvoiceID = @id
    RETURN

我们应用程序的数据访问层根据结果构建对象图（O/R Mapper 风格）。

我遇到的问题是我们有许多不同的发票选择存储过程。 它们都返回相同的结构，只是针对不同的选择标准。 例如，我还有：

CREATE PROCEDURE hd_invoice_selectAllForCustomer(@customerID INT) AS
    SELECT * FROM Invoice WHERE CustomerID = @customerID
    SELECT * FROM InvoiceItem WHERE InvoiceID IN 
        (SELECT InvoiceID FROM Invoice WHERE CustomerID = @customerID)
    SELECT * FROM InvoiceComments WHERE InvoiceID = @id
        (SELECT InvoiceID FROM Invoice WHERE CustomerID = @customerID)
    RETURN

我还有很多其他的，包括：

hd_invoice_selectActive()
hd_invoice_selectOverdue()
hd_invoice_selectForMonth(@year INT, @month INT)

我对很多概念（客户、员工等）都有相同的模式，

我们最终复制了大量代码，维护非常困难。 当一个概念的“结构”发生变化时，我们必须去修复所有过程，这很容易出错。

所以我的问题是：在场景中重用代码的最佳方式是什么？

我们提出了一个使用临时表的解决方案。 但它不是很优雅。 我会让您分享您的想法，如有必要，我将在即将发布的帖子中发布我的解决方案的详细信息，以获取您对该方法的评论。

谢谢

Answer 1

将其发布为第二个答案，因为这是一种不同的方法。 如果您使用的是 SQL Server 2008：

CREATE TYPE InvoiceListTableType AS TABLE 
(
    InvoiceId INT
);
GO

CREATE PROCEDURE hd_invoice_selectFromTempTable
(
    @InvoiceList InvoiceListTableType READONLY
)
AS
BEGIN
    SELECT * FROM Invoice WHERE InvoiceID IN
        (SELECT InvoiceId FROM @InvoiceList)

    SELECT * FROM InvoiceItem WHERE InvoiceID IN 
        (SELECT InvoiceId FROM @InvoiceList)

    SELECT * FROM InvoiceComments WHERE InvoiceID IN
        (SELECT InvoiceId FROM @InvoiceList)

    RETURN
END
GO

CREATE PROCEDURE hd_invoice_select(@id INT) AS
BEGIN
    DECLARE @InvoiceList AS InvoiceListTableType;

    SELECT id AS ID 
        INTO @InvoiceList

    EXEC hd_invoice_selectFromTempTable(@InvoiceList)
    RETURN
END
GO

CREATE PROCEDURE hd_invoice_selectAllForCustomer(@customerID INT) AS
BEGIN
    DECLARE @InvoiceList AS InvoiceListTableType;

    SELECT invoiceID as ID
        INTO @InvoiceList
        FROM Invoice WHERE CustomerID = @customerID

    EXEC hd_invoice_selectFromTempTable(@InvoiceList)
    RETURN
END
GO

CREATE PROCEDURE hd_invoice_selectAllActive AS
BEGIN
    DECLARE @InvoiceList AS InvoiceListTableType;

    SELECT invoiceID as ID
        INTO @InvoiceList
        FROM Invoice WHERE Status = 10002

    EXEC hd_invoice_selectFromTempTable(@InvoiceList)
    RETURN
END
GO

Answer 2

对于这种特定场景，“最佳”方法是使用某种代码生成。 提出某种约定并将其插入代码生成器中。

Answer 3

您是否尝试过在主过程的参数列表中放置超过 1 个查询参数类型？ 我只编写了覆盖发票表的过程，您需要为其他表扩展它。

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来调用，对于基于@id且@customerId为NULL的特定InvoiceID（或者只是将其全部保留），或者对于基于@customerId离开的特定客户@id 为 NULL 或将其从查询中排除。

您还应该查看视图和表值用户定义函数。 您可以将它们放入您的过程中，以将一些逻辑从过程中包装出来，以便它们可以在一个地方共享和维护。 在视图/函数中拥有一些逻辑还允许您像处理表一样处理查询窗口中的数据。

Answer 4

我是最先提出这个问题的人。 我在这里回答我自己的问题是为了让您了解我使用的代码重用解决方案并获取您对该方法的评论。 如果这个答案获得很多赞成票，我将选择它作为最终答案。

这种方法有效且易于使用。 我不知道它是否会对性能产生影响，因为它严重依赖临时表。

对于我的应用程序中的每个概念，我都有一个如下所示的存储过程：

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

然后我根据需要创建尽可能多的选择存储过程：

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

您认为这种方法怎么样？ 它有点类似于 AlexKuznetsov 的答案，但我使用临时表而不是 BLOB 参数。

Answer 5

这是存储过程的主要问题之一，也是人们不喜欢它们的原因。

我从未找到或见过解决它的方法。

Answer 6

我已经开始使用代码生成器生成的存储过程来进行基本的 CRUD。 我使用存储过程来处理报告或复杂的 SQL 工作。

我还有一个与您的问题无关的建议 - 不要使用 IN 子句，而是在 SQL 语句中使用 EXISTS 子句。

Answer 7

我继承了一个以前使用临时表方法的应用程序，我同意它非常混乱。

在该项目中，我们能够通过将临时表替换为包含我们所需的“对象”的视图来删除它们，然后更新存储过程以查询这些视图。

也许这也适用于您的情况。

Answer 8

在某些情况下，我使用视图来重用“代码”。 例如过滤器、活动项目、过时的项目等等......

Answer 9

也许您应该学习使用联接。 您可以将三个表的基本连接放在一个视图中，然后使用处理不同参数的 sp 进行查询。 另外，一般情况下，您不应该在生产代码中使用 select * 。 仅返回您在这种情况下实际需要的几列，您的整个系统的性能将会更好。 另外，当人们改变你的结构时，你不会得到意想不到的结果。

Answer 10

我有时会分两步完成：

我提出一个 InvoiceID 列表。 然后我用这个列表作为参数调用我的存储过程。

在 2005 年，我们没有表值参数，因此我将 ID 列表打包在二进制 BLOB 中并将其提交到 SQL Server，如下所述：SQL Server 2005 中的数组和列表

您还可以以逗号分隔的列表（有点慢）或固定的串联形式提交 ID 列表。宽度字符串表示（更快）。