title: Using check constraints in a SQL Server stored procedure
body: <p>I want to create a check constraint on a stored procedure where the invoice date can't go before January 1st 1990 and can't go ahead of December 31st 9999.</p>
  
  <p>Right now I have my SQL script setup like this:</p>
  
  <pre><code>CREATE PROCEDURE dbo.[getBalanceByDueDateRange]
      @MINDATE DATETIME = '1990-01-01 00:00:00',
      @MAXDATE DATETIME = '9999-12-31 00:00:00'
  AS       
      SELECT  
          i.InvoiceID AS 'Invoice ID',
          v.VendorID AS 'Vendor ID',
          CONVERT(VARCHAR (12), i.InvoiceDueDate, 107) AS 'Invoice Due Date',
          ABS(i.InvoiceTotal - i.PaymentTotal) AS 'Balance'
      FROM 
          dbo.[Vendors] v
      INNER JOIN 
          dbo.[Invoices] i ON v.VendorID = i.VendorID
      WHERE   
          i.InvoiceDueDate BETWEEN ISNULL(@MINDATE, i.InvoiceDate) AND ISNULL(@MAXDATE, i.InvoiceDate)
      ORDER BY 
          i.InvoiceDueDATE ASC;
  </code></pre>
  
  <p>What I would like it to do is return an error if the <code>MINDATE</code> or the <code>MAXDATE</code> are not within that range. I'm thinking I might have to use IF/ELSE statements but is there another way like using <code>CHECK CONSTRAINT</code>? The stored procedure does work (in that it returns nothing) but I want it to be a little bit more effective and professional. If you have any suggestions I would love to hear it.</p>