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

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.