title: Use current day as default
body: <p>I'm working on a stored procedure that takes 3 input parameters. The first one is required but the other 2 are dates and are not required. I want the date parameters to default to the current day. Here is what I have so far:</p>
  
  <pre><code>USE [MyDB]
  GO
  
  /****** Object:  StoredProcedure [dbo].[p_qIMO_TEST_2]
  
  Example: exec [dbo].[p_qIMO_TEST_2] 'DS5264000001','20090101','20100101'
  
  */
  
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('qIMO_TEST_2'))
       exec('CREATE PROCEDURE [dbo].[qIMO_TEST_2] AS BEGIN SET NOCOUNT ON; END')
  GO
  
  alter PROCEDURE [dbo].[p_qIMO_TEST_2] 
       @i_InstrumentID VARCHAR(15) = NULL,
       @i_DateLow      VARCHAR(15) = '20090101',  
       @i_DateHigh     VARCHAR(15) = '20291231'   
  AS
  IF @i_InstrumentID IS NULL
  BEGIN
      PRINT 'Error: You must specify a valid Instrument ID'
      RETURN
  END
       SELECT * FROM
       (
              SELECT 
                   out_interface_id,
                   msg_id, 
                   CAST(xml_msg as XML).value(
                      '(//InstrumentID)[1]','nvarchar(MAX)') AS InstrumentID,
                   msg_type,
                   xml_msg,
                   CAST(xml_msg AS XML) as [Quick_XML], 
                   date_received,
                   status,
                   last_modified,
                   environment,
                   transaction_closed_date
              FROM MyTable
              WHERE msg_type IN ('ABC','DEF') 
              AND date_received &gt;= @i_DateLow
              AND date_received &lt; DATEADD(DAY,1,@i_DateHigh)  -- Need to add 1 to the DateHigh for 
                                                                                                          -- date range criteria to work properly (&gt;= and &lt;)
       ) x
       WHERE (x.InstrumentID = @i_InstrumentID OR x.InstrumentID = NULL)
       ORDER BY date_received DESC
  RETURN
  GO
  </code></pre>
  
  <p>Right now, I'm just using an arbitrary range of dates but it takes a long time to return a result. Looking for any suggestions anyone can offer. I'm using MS SQL server 2008 r2.</p>