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

We use SQL Server 2005. All our data access is done through stored procedures. Our selection stored procedures always return multiple result sets.

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

For instance:

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

Our application's data access layer builds an object graph based on the results (O/R Mapper style).

我们的应用程序的数据访问层基于结果(O / R Mapper样式)构建对象图。

The problem I have is that we have many different invoice selection stored procs. They all return the same structure, only for different selection criteria. For instance, I also have:

我遇到的问题是我们有许多不同的发票选择存储过程。它们都返回相同的结构,仅用于不同的选择标准。例如,我也有:

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

and I have many others including:

我还有很多其他的,包括:

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

and I have the same pattern for a lot of concepts (Customers, Employees, etc)

对于很多概念(客户,员工等)我有相同的模式

We end up copying a lot of code and maintenance is really hard. When the "structure" of a concept changes, we have to go and fix all procs and it's very error prone.

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

So my question is: What is the best way to reuse the code in the scenario?

所以我的问题是:在场景中重用代码的最佳方法是什么?

We came up with a solution that uses temp tables. But it's not very elegant. I'll let you share your ideas and if necessary I will post the detail of my solution in an upcoming post to get your comments on that approach.

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

Thanks

10 个解决方案

#1

Posting this as a second answer because it is a different approach. If you are using SQL Server 2008:

将此作为第二个答案发布,因为它是一种不同的方法。如果您使用的是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

#2

The "best" way for this specific scenario would be to use some sort of code generation. Come up with some sort of convention and plug it into a code generator.

这种特定场景的“最佳”方式是使用某种代码生成。想出某种约定并将其插入代码生成器。

#3

Have you tried putting more than 1 query parameter type in the list of parameters for your main proc? I only wrote the proc to cover the Invoice table, you will need to extend it for your additional tables.

您是否尝试在主proc的参数列表中添加多个查询参数类型?我只编写了proc来覆盖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

This proc can be called wide open by sending @id and @customerId as NULLs, for a specific InvoiceID based on @id with @customerId as NULL (or just leave it off all together), or for a specific customer based on @customerId leaving @id as NULL or exclude it from the query.

通过将@id和@customerId作为NULL发送,对于基于@id的特定InvoiceID,将@customerId作为NULL(或者只是将它们全部放在一起),或者基于@customerId离开的特定客户,可以将此proc称为open open @id为NULL或从查询中排除它。

You also should look at views and Table-Valued User-Defined Functions. You can put these in your procs to wrap up some of the logic away from the procs so they can be shared and maintained in a single place. Having some of the logic in views/functions also allows you to deal with the data in a query window as if it were a table.

您还应该查看视图和表值用户定义函数。你可以把它们放在你的过程中,将一些逻辑从procs中包起来,这样它们就可以在一个地方共享和维护。在视图/函数中使用某些逻辑还允许您在查询窗口中处理数据,就像它是一个表一样。

#4

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:

对于我的应用程序中的每个概念,我有一个这样的storec proc:

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.

您如何看待这种方法?它有点类似于AlexKuznetsov的答案,但我使用临时表而不是BLOB参数。

#5

This is one of the main problems with stored procedures and why people don't like them.

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

I've never found or seen a way around it.

我从来没有找到或看到过这种方式。

#6

I have started to use stored procedures generated by Code Generators for my basic CRUD. I use stored procs for reports or complex SQL work.

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

I have a suggestion unrelated to your question as well - instead of using the IN clause, use the EXISTS clause in your SQL statements.

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

#7

I've inherited an application that used the temp table approach before and I agree that it's very messy.

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

On that project we were able to remove a lot of the temp tables by replacing them with Views that contained the desired 'objects' we needed then we updated our stored procedures to query off of those views.

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

Perhaps that may work in your situation as well.

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

#8

In some cases I use VIEWS to reuse "code". In cases as filters, active items, outdated things, and so on...

在某些情况下,我使用VIEWS重用“代码”。在过滤器,活动项目,过时的东西等情况下......

#9

Maybe you should learn to use joins. You could put the basic join of the three tables in a view and just query that with the sp handing the different parameters. Also, you should not in general use select * ever in production code. Only return the few columns you actually need in the circumstances and your whole system will be better performing. Plus you won't have unintended results when people change the structure on you.

也许你应该学习使用连接。您可以将三个表的基本连接放在视图中,只需使用sp处理不同的参数即可。此外,您通常不应该在生产代码中使用select * ever。只返回实际需要的几列,整个系统的性能会更好。另外,当人们改变你的结构时,你不会有意想不到的结果。

#10

I sometimes do it in two steps:

我有时会分两步完成:

I come up with a list of InvoiceID. Then I call my stored procedure with this list as a parameter.

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

On 2005 we don't have table valued parameters, so I pack my list of IDs in a binary BLOB and submit it to SQL Server, as described here: Arrays and Lists in SQL Server 2005

在2005年,我们没有表值参数,所以我将我的ID列表打包在二进制BLOB中并将其提交给SQL Server,如下所述:SQL Server 2005中的数组和列表

You can also submit a list of IDs as a comma-separated list (somewhat slow) or as a concatenation of fixed width string representations (much faster).

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