title: Can I default a sql stored procedure parameter to a dynamic expression?
body: <p>I want to have a "@myDate" parameter in a stored procedure that defaults to 2 years prior to today if nothing else is specified. I tried doing something like this in my procedure definition:</p>
  
  <pre><code>CREATE PROCEDURE myProcedure(   @param1 int,
                                  @param2 varchar(20),
                                  @param3 int = null,
                                  @myDate datetime = dateadd(year,-2,getDate()) )
  </code></pre>
  
  <p>I'm getting the following syntax error:</p>
  
  <pre><code>Incorrect syntax near '('.
  </code></pre>
  
  <p>Does sql server allow you to set dynamic expressions as default parameter values? If not, how can I get around this (other than the clumsy <code>IF @myDate is null SET @myDate=...</code>)?</p>