Error when using GetDate() in SP default parameter value 1

[mkrausnick]

When I do this:

CREATE PROCEDURE [dbo].[MyProc] @p_EndDate DateTime = GetDate()

I get this error:

Msg 102, Level 15, State 1, Procedure ap_Ucla_MedicalHome, Line 1
Incorrect syntax near '('.

If I take away the parentheses it compiles but when I execute it with no supplied date I get:

Msg 241, Level 16, State 1, Procedure ap_Ucla_MedicalHome, Line 0
Conversion failed when converting datetime from character string.

If execute the SP and supply a date, it runs with no errors.
What am I doing wrong?


Mike Krausnick
Dublin, California

 

[gmmastros]

I suggest...

Set the default value to NULL. Then... inside the procedure....

Set @p_EndData = Coalesce(@p_EndDate, GetDate())



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mkrausnick]

Beautiful! Worked like a charm.

Mike Krausnick
Dublin, California