Using default values for stored procedure parameters

  • Hi,

    I have been trying to use default values as parameters of a stored procedure.

    All the explanations tell about using a NULL value for a procedure parameter to make it optional.

    I have also read that any value that is compatible with the datatype of the parameter could have been set.

    Following is what I am trying to do:

    ------------------------------------

    create procedure Proc_Def_Param

    @startDate datetime = NULL,

    @endDate datetime = getdate() ------------------ I

    As

    Begin

    .....

    .....

    End

    ------------------------------------

    However I am unable to parse this code. The error says:

    "Incorrect syntax near '('."

    which takes me to line I marked above.

    The same proc parsed when I put in actual date. Eg '12-26-07'

    Could anyone guide me on supplying default values to a stored procedure.

    Is it possible to provide only explicit values to the stored procedure's default parameters?

    Thank you.

  • You can't assign a non-deterministic value as the default parameter like that. Instead, assign it something like NULL, and then inside the stored procedure, set it to Getdate() if it's currently set to NULL.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply