views:

39

answers:

2

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() ...
+1  A: 

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
why i can't use it .. is it won't be easy to sql engine to do ?
Space Cracker
because that's the way it works!
Mitch Wheat
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
Space Cracker
it has to be a constant value.
Mitch Wheat
i didn't find what i need but thanks mitch
Space Cracker
A: 

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

TheVillageIdiot