title: How to reuse code in SQL stored procedures?
body: <p>We use SQL Server 2005. All our data access is done through stored procedures. Our selection stored procedures always return multiple result sets. </p>
  
  <p>For instance:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Our application's data access layer builds an object graph based on the results (O/R Mapper style). </p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and I have many others including:</p>
  
  <pre><code>hd_invoice_selectActive()
  hd_invoice_selectOverdue()
  hd_invoice_selectForMonth(@year INT, @month INT)
  </code></pre>
  
  <p>and I have the same pattern for a lot of concepts (Customers, Employees, etc)</p>
  
  <p>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. </p>
  
  <p>So my question is: What is the best way to reuse the code in the scenario?</p>
  
  <p>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.</p>
  
  <p>Thanks</p>