RANT: Ok, so I understand that sometimes there are reasons why some things are allowed and others are not. However, I haven't been able to justify this one.
If I want to default a stored procedure parameter to today's date if left empty, I can't do it. Reason being only a constant or Null can be a default value of a parameter. Ok, fine. I'll set the default to null then evaluate if it's null in the SP and set to GetDate(). It's a workaround and another couple lines of code that don't need to be there.
My question is this though: Why can I set a column in a table to a default value of GetDate() but not in a stored procedure? Even better, if you try to create a stored procedure with this assignment by default, it gives you some strange error about ")" or whatever line precedes the assignment and doesn't explain that a constant wasn't found.
4 comments:
Hi Man
Its as simple as that
1. Set the default value to be null in the sproc declaration
2. Check whether the datetime variable is null or not using if condition
3. If it is null, set current date to the variable using getDate()
See the ex below
Create Procedure asp_LoadEvents
@EventTime as DateTime = Null
As
Begin
If @EventTime = Null
Begin
Set @EventTime = getDate()
End
Select u.UserId
,u.LoginName
,u.FirstName
,u.MiddleName
,u.LastName
,u.EmailId
............... and so on
I hope that it answered your question
Regards,
--Kiran Chand--
Software Engineer
Photon Infotech Private Limited,
India
O.T: Sorry for using the local blogging credentials
Hi Kiran,
Thanks for the confirmation. I was hoping that I could use a default function instead of defaulting to null. I agree it isn't that big of a deal to evaluate null then set.
Cheers,
Chad
Thanks Kiran, Minor Correction for the Example for Others Referencing...
If @EventTime = Null
should be changed to
If @EventTime is Null
That was really buging me!!! There is nothing about that at Books Online!
Thanks!
Post a Comment