Default value of stored procedure parameter

Hi,

This works:

CREATE PROCEDURE MyProc

@.Date smalldatetime = '2005-01-01'

AS

...

But this does not

CREATE PROCEDURE MyProc

@.Date smalldatetime = GETDATE()

AS

...

I'm talking about sql2005. Can anyone help how to overcome this?

You'd have to set it inside the proc not at the definition level. Leave the default as NULL. Inside the proc check if the @.Date IS NULL, then assign the Getdate() to it. Let me try to phrase it "You cannot assign non-deterministic value to a parameter in proc definition".

|||

Not exactly the same, but usually works:

CREATE PROCEDURE MyProc

@.Date smalldatetime = NULL

AS

IF @.Date IS NULL SET @.Date=GetDate()

|||

ndinakar:

"You cannot assign non-deterministic value to a parameter in proc definition".

I knew about solution you offered, but real answer I was looking for is sentence I quoted.

Thank you.