title: SQL Sproc Parameters: Setting Default Date value
body: <p>I am trying to create a Stored Procedure that will be used for a Report and I want the 2 date parameters to have a DEFAULT value of today's date and 1 month prior.</p>
  
  <p>Is the below the proper way to do this?  I was reading elsewhere that I should use COALESCE...(<a href="https://stackoverflow.com/questions/470664/sql-function-as-default-parameter-value/470670#470670">SEE HERE</a>)This is a touchy production system so I wanted to double check before I went forward.</p>
  
  <pre><code>CREATE PROCEDURE spCaseNoteReport 
  -- Add the parameters for the stored procedure here
  @ContactStartDate DateTime = null,
  @ContactEndDate DateTime = null
  AS
  IF @ContactStartDate is null
  SET @ContactStartDate = dateadd(m,-1, CONVERT(date, GETDATE()))
  IF @ContactEndDate is null
  SET @ContactEndDate = CONVERT(date, GETDATE())
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  -- Insert statements for procedure here
  SELECT (id.LastName + ', ' + id.FirstName) AS 'MemberName'
        ,c.ContactDate
        ,Li.ItemDescription AS 'Location'
        ,c.PresentAtContact
        ,c.ContactDetails   
        ,c.InsertUser
        ,c.TimeSpentUnits
  
  FROM dbo.tblCaseNotes c
      inner join dbo.tblIdentificationMap id
          on c.PersonID = id.PersonID
      left outer join dbo.tblCaseNoteContactLocation L
          on c.Casenoteid = L.Casenoteid
              inner join dbo.tblCaseNotesMaintItem Li
                  on L.ContactLocationID = Li.ItemID
  
  WHERE c.ContactDate BETWEEN @ContactStartDate AND @ContactEndDate
  
  ORDER BY MemberName, c.ContactDate, c.InsertUser
  </code></pre>
  
  <p>END</p>
  
  <hr>
  
  <h2>continued</h2>
  
  <p>So when I actually tried to run the <code>CREATE PROCEDURE</code> for this I get the following errors --></p>
  
  <p><code>Msg 243, Level 16, State 1, Procedure spCaseNoteReport, Line 12
  Type date is not a defined system type.
  Msg 243, Level 16, State 1, Procedure spCaseNoteReport, Line 14
  Type date is not a defined system type.</code></p>