July 28, 2009 at 12:25 am
Hi every one. My logic is failing me.... still scratching my head...
I have 2 variables.
The proc should allow both variables to be null.
But if one is specified, then the other should be specified as well.
And I do not want very complex validion code. it should be simple small blocks of code.
For instance, one validation is to check that the unit is passed to the proc
Simple, and effective
if isnull(UnitID,0) = 0
Begin
Raiserror('Please Specify Unit', 16,1)
Goto BatchEnd
End
Now back to the tricky validation :
I can come up with multiple ways to do the validation, but they will all be at least one full page of code, with heavy nested if statements and multiple goto statements. I hate Goto. Will this be the only way, or is there a simpler solution?
The following should fail.
[Code]
Declare @RetailDateFrom datetime
Declare @RetailDateTo datetime
Set @RetailDateFrom = '2009-07-28 12:00:00'
Set @RetailDateTo = null
[/Code]
Now these 2 shoud pass:
[Code]
Declare @RetailDateFrom datetime
Declare @RetailDateTo datetime
Set @RetailDateFrom = '2009-07-01 12:00:00'
Set @RetailDateTo = '2009-07-28 12:00:00'
[/Code]
AND
[Code]
Declare @RetailDateFrom datetime
Declare @RetailDateTo datetime
Set @RetailDateFrom = null
Set @RetailDateTo = null
[/Code]
July 28, 2009 at 12:39 am
can a moderator please delete one post of the duplicates. After posting, I got an error:
The following error occurred...
Sorry the application encountered an unexpected error. Information about this error has been logged. If you continue to receive this message please contact the board administrator.
So I posted from another browser. did not realize it actually posted
July 28, 2009 at 1:09 am
This is what I have, but it always prints success.
Declare @RetailDateFrom datetime
Declare @RetailDateTo datetime
Set @RetailDateFrom = '2008-07-01 12:00:00'
Set @RetailDateTo = '2008-07-28 12:00:00'
if ((@RetailDateFrom is null) AND (@RetailDateFrom is null))
OR ((@RetailDateFrom is not null) AND (@RetailDateFrom is not null))
Begin
print 'Success'
End
Else
begin
Print 'Fail'
End
**EDIT**
Ths was the the ultimate Homer Simpson "DOH!" moment of my career. The above works.
It is simply a fact that the second parameter in both the 'AND' as well as the 'OR' part of the if still pointed to the 1st parameter.
DOH!
July 28, 2009 at 1:35 am
Ok one more question.
How to i turn this if around to look from the other anlge?
Declare @RetailDateFrom datetime
Declare @RetailDateTo datetime
Set @RetailDateFrom = '2008-07-01 12:00:00'
Set @RetailDateTo = '2008-07-28 12:00:00'
if (@RetailDateFrom is null AND @RetailDateTo is null)
OR (@RetailDateFrom is not null AND @RetailDateTo is not null)
Begin
print null
End
Else
begin
Raiserror('Please either specify no Retail Dates, or Both Retail Dates', 16,1)
End
I tried If NOT and that does not work out.
July 28, 2009 at 8:41 am
Not sure I understand what you mean when you say turn it around from the other angle? The code you posted seems to work fine. I changed it to use print commands for both and it seems to work. How did you want to change it?
Declare @RetailDateFrom datetime
Declare @RetailDateTo datetime
Set @RetailDateFrom = GETDATE()
Set @RetailDateTo = GETDATE()
if (@RetailDateFrom is null AND @RetailDateTo is null)
OR (@RetailDateFrom is not null AND @RetailDateTo is not null)
Begin
print ('It Worked')
End
Else
begin
Print('Please either specify no Retail Dates, or Both Retail Dates')
End
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply