Sunday, March 25, 2012

Default date problem

Hi,

Good Day!

In my sproc, I m trying to set a default value for a parameter, but it's sending me an error. It seems like having probs with brackets!

Code Snippet

@.Date DateTime = GetDate(),

Code Snippet

Msg 102, Level 15, State 1, Procedure usp_Receive_Add, Line 8
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure usp_Receive_Add, Line 40
Must declare the scalar variable "@.ProductID".

Please tell me what's the prob! I m trying to set todays date to the parameter if nothing was supplied in the @.Date parameter.

Regards

Kapalic

Use the following logic to set the current date..

Code Snippet

Create proc MyProc
(
@.Date datetime = '1900-01-01'
)
as
Begin
Select @.Date = Case When @.Date <> '1900-01-01' Then @.Date Else Getdate() End
Select @.Date
End


go


Exec MyProc '2/2/2007'
Exec MyProc

|||How about setting default value of @.date to NULL and set it to GetDate() in store procedure body? I just wandering 1900-1-1 is valid value. here is valid value of DateTime according SQL Server 2005 document.

datetime

January 1, 1753, through December 31, 9999

|||

Yes.. You can do it with NULL.

Suppose if you want to store the explicit null value on your table then this logic wont work. RITE?

So we are setting some default value which we are assuming that it never passed from our UI.

1900-01-01 is valid value only. it is with in the given range Buddy.. It is a typical sql coders starting value [Cast(0 as Datetime)]

|||

In a variable declaration, you can set a variable to a constant, e.g., a value.

However, you cannot set it to the results of a function. Getdate() is a function.

As suggested, if you wish to make the parameter optional, set a default value of '01/01/1900', and then if you wish to set it to the current date/time, after entering the procedure code (after 'AS'), set the parameter = getdate().

No comments:

Post a Comment