SQL as the default parameter value?

Question

I tried to change the default parameter value as follows:

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

and the whole SQL precompiler gave me this error:

Msg 102, Level 15, State 1, Procedure my_sp, Line 8 Invalid syntax near '('.

I have already created this procedure. (I'm not sure if this is relevant.) I used a default value of zero and checked it later, but that doesn't seem right. Can I do this on one line?

---

Update: I left the MSDN Stored Procedure Parameter Description :

[= default] The default value for the parameter. If a default value is specified, the function can be executed without specifying a value for this parameter.

Note:
Default parameter values ​​can be specified for CLR functions, with the exception of the varchar (max) and varbinary (max) data types.

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

Am I reading this wrong?

Thank you very much.

Answer 1

The default value for the stored procedure parameter must be constant . You will need to do the following ...

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

Answer 2

I do not think it is possible, you should use the value of literal (constant) as the default value.

However, you can do this:

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

Answer 3

You can try the following:

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

Answer 4

I assume that you are using Microsoft SQL Server out of square brackets in your example.

From MSDN :

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

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

Answer 5

This value is not deterministic and cannot be used.

Answer 6

Sentence:

Set the default value to NULL

Make default GETDATE() in the interface.