How to reuse code in SQL stored procedures?

Question

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

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).

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

Answer 1

Posting this as a second answer because it is a different approach. If you are using 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