12

I want to know why I can't set default value to SP datetime parameter to getdate() as below :

Create PROCEDURE [dbo].[UPILog]
(
    @UserID bigint,
    @ActionID smallint,
    @Details nvarchar(MAX) = null,
    @Created datetime = getdate()
)

if I try to save it will give me a compiler error

    Msg 102, Level 15, State 1, Procedure UPILog, Line XX
    Incorrect syntax near '('.

EDIT : I know that i can do it like below

Create PROCEDURE [dbo].[UPILog]
(
    @UserID bigint,
    @ActionID smallint,
    @Details nvarchar(MAX) = null,
    @Created datetime = null
)
AS
if @Created is null
    SET @Created=getdate() ...
Amr Badawy
  • 7,453
  • 12
  • 49
  • 84

5 Answers5

20

If you want to use the @Created as a default though, then set null as the default parameter value and set the @Created parameter with getdate() if it comes with null in your sp.

    ...
    @CreateDate datetime = null
)
AS
    if @CreateDate is null
        set @CreateDate = getdate()
...

EDIT: There is also the documentation explanation: https://stackoverflow.com/a/53832983/197652

Emre Guldogan
  • 590
  • 9
  • 19
4

You can't use a function call as a default parameter value.

It's easy to work around: set your calling parameter to getdate() if not set.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • why i can't use it .. is it won't be easy to sql engine to do ? – Amr Badawy Jun 19 '10 at 10:25
  • Is there any explanation why it can't accept this .. or it's a just as SQL SERVER rule that can't call function as a default parameter – Amr Badawy Jun 19 '10 at 10:40
  • it has to be a constant value. – Mitch Wheat Jun 19 '10 at 10:54
  • 2
    @SpaceCracker the reason I believe is that what you'd be doing (if it were to allow you) is to run the getdate() function at the time your code was compiled (keep in mind we're compiling code) and store the result of that one run of getdate() as the default value... something you don't want. What you want is for it to run the getdate function any time your UPILog function is called, which should/can-only be done in the function body. – jinglesthula Jan 11 '13 at 21:39
4
  ....     
  @CreateDate datetime = null
)

And then use COALESCE() like below - 

COALESCE(@CreateDate, getdate())
toothful
  • 882
  • 3
  • 15
  • 25
1

in simplest terms it has to be some constant value and GetDate() is a function call.

TheVillageIdiot
  • 40,053
  • 20
  • 133
  • 188
0

You can't use a function as a parameter value. What I do is I set the parameter to some out of scope date like '1900-01-01' and then I check for it in the proc. for example:

CREATE PROC uspDataCompare  
    @CompareDate    DATE = '1900-01-01'
AS

IF  @CompareDate = '1900-01-01' SET @CompareDate = CONVERT(DATE, GETDATE())
Ilan
  • 29
  • 3