SQL function as default parameter value?

Question

Hello everyone,

First I apologise if this has been asked. I did a quick search and didn't find anything related.

Here is my scenario. I tried to do this:

ALTER PROCEDURE [dbo].[my_sp]
@currentDate datetime = GETDATE()

All the SQL pre-compiler gives me back is this error: Msg 102, Level 15, State 1, Procedure my_sp, Line 8 Incorrect syntax near '('.

I have already created the procedure. I was using a null default value and checking for that later but it doesn't seem proper. Can I do this in one line?

UPDATE: First thank you all for the quick responses. Holy cow! Second I was going off of MSDN's description of stored procedure parameters here: http://msdn.microsoft.com/en-us/library/ms186755.aspx

[ = default ] Is a default value for the parameter. If a default value is defined, the function can be executed without specifying a value for that parameter.

Note:
Default parameter values can be specified for CLR functions except for the varchar(max) and varbinary(max) data types.

When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.

Did I read this wrong? Many thanks.

UPDATE 2: Replied to two answers with comments. Not sure if they should go up here.

Answer 1

I don't think that is possible, you have to use a literal (constant) value as the default.

However you can do this:

Set @currentDate = Coalesce(@currentDate , GetDate())

Answer 2

Default value for stored procedures parameter have to be constants. You'd need to do the following...

ALTER Procedure [dbo].[my_sp]
@currentDate datetime = null
AS
IF @currentDate is null
SET @currentDate = getdate()

Answer 3

what in the world are you doing? why would you wrap getdate() in a sproc?

Answer 4

That value is not deterministic and cannot be used

Answer 5

I infer you're using Microsoft SQL Server from the square brackets in your example.

From MSDN:

Only a constant value, such as a character string; a scalar function (either a system, user-defined, or CLR function); or NULL can be used as a default.

The function GETDATE() returns a different value from time to time, so it is not a constant expression.

Answer 6

Suggestion:

Set the default to NULL.

Do the Default GETDATE() in the frontend.

:-)

Answer 7

Set @CurrentDate=IsNull(@CurrentDate,GetDate())