How do I set a default datetime parameter in a stored procedure?

Question

I have declared a stored procedure in Sybase, and one of the parameters is of type datetime. Now I want to assign this datetime a default value.

Here's the declaration:

create procedure Procedure 
(
    @fromDate datetime = getdate()
)
...

However Sybase is giving me an error

Number (102) Severity (15) State (1) Server (SERVER) Procedure (Procedure) Incorrect syntax near '('.

Is it possible to do this? If not, is there a workaround?

Answer 1

You can not use a function call in a default variable assignment (as you found out).

Set the default to Null, and put an assignment first thing in the stored procedure.

  create procedure Procedure 
  (
      @fromDate datetime = NULL
  )
  begin

      set @fromDate = coalesce( @fromDate , getdate() ) 

  end