Can I use the sql stored procedure parameter for dynamic expression by default?

Question

I want to have the @myDate parameter in a stored procedure, which defaults to 2 years until today if nothing is specified. I tried to do something similar in my procedure definition:

CREATE PROCEDURE myProcedure( @param1 int, @param2 varchar(20), @param3 int = null, @myDate datetime = dateadd(year,-2,getDate()) ) 

I get the following syntax error:

 Incorrect syntax near '('. 

Does the sql server allow you to set dynamic expressions as default parameter values? If not, how can I get around this (except for the clumsy IF @myDate is null SET @myDate=... )?

Answer 1

You cannot use an expression as the default, and there is no really elegant way to do this.

You can use isnull or coalesce instead of the if :

 set @myDate = isnull(@myDate, dateadd(year, -2, getdate())) 

Answer 2

Not.

From docs :

The default value must be constant or it can be NULL