Can't get IF-ELSE logic working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bev Kaufman - 28 Jul 2008 21:08 GMT

I've been struggling with a puzzling piece of code. Maybe someone else can
figure it out. The gist is that it's telling me that the first branch runs
(or most of it) whether the condition is true or false

IF (@PastDue + @OpenShipAmt) > @CreditLimit
    -- customer flunks credit check
    BEGIN
    PRINT 'customer flunks '
    SET @DontPrint = 1
    IF @NoUpdate = 0    -- put shipper on credit hold
        BEGIN
        PRINT 'about to update'
        UPDATE SOShipHeader SET
            User7 = '1',
            User10 = GETDATE()
            WHERE ShipperID = @ShipperID
                AND CpnyID = @CpnyID
        END    -- if no update
    END    -- if customer flunks
    ELSE    -- customer has passed credit check. If shipper is on hold,
        --    then release it
        BEGIN
        PRINT 'customer passes'
        IF @NoUpdate = 0    -- put shipper on credit hold
            UPDATE SOShipHeader SET
                User7 = '0',
                User10 = 1/1/1990
                WHERE ShipperID = @ShipperID
                    AND CpnyID = @CpnyID
        END -- else statement

When (@PastDue + @OpenShipAmt) = 500 and @CreditLimit = 1000, what I should
see is:
customer passes
1 record updated with results that User7 = 0
Instead, I see
about to update
1 record updated with results that User7 = 1
It's running the first branch of the if statement but not the first line in
it (print customer flunks ).
If the data changes so that (@PastDue + @OpenShipAmt) = 1500, I will see
customer flunks
about to update
1 record updated with results that User7 = 1

It appears that when the condition is false, it will bypass the first line
of the true block, instead of the entire block, while the false block will
never run. Since I enclose my blocks in begin and end syntax, this should
not be happening.

Can anyone see what I'm doing wrong?

Reply to this Message

Bev Kaufman - 28 Jul 2008 22:37 GMT

It's gotten wierder. I copied the code to a QA window, declared and set the
necessary variables, and the code worked. Returning to the stored procedure,
it still failed, so I looked elsewhere. One of the things I checked was
begin-end pairings, and I found I was missing and end statement further down
the code, but adding it back in caused a compile error.
I went through the tedious process of commenting out huge blocks of code and
slowly adding in back in, until I identified the culprit, which was the same
block of code that I printed here and that worked correctly in QA. In the
end, I ended up typing in my code practically verbatim, right down to the
comments, and now it works. But what made it not work in the first place? I
lost 4 hours trying to debug this, and am now way over budget.

> I've been struggling with a puzzling piece of code. Maybe someone else can
> figure it out. The gist is that it's telling me that the first branch runs
[quoted text clipped - 47 lines]
>
> Can anyone see what I'm doing wrong?

Reply to this Message

Dan Guzman - 29 Jul 2008 02:57 GMT

> But what made it not work in the first place? I
> lost 4 hours trying to debug this, and am now way over budget.

I don't think we can answer your question since you never posted the
original non-working code.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> It's gotten wierder. I copied the code to a QA window, declared and set
> the
[quoted text clipped - 72 lines]
>>
>> Can anyone see what I'm doing wrong?

Reply to this Message

Bev Kaufman - 29 Jul 2008 14:55 GMT

Actually I did, in the first post, which was then copied into each succeeding
post. Or rather I included the trouble-making if-else block. The full
stored proc is over 3 pages long.

> > But what made it not work in the first place? I
> > lost 4 hours trying to debug this, and am now way over budget.
[quoted text clipped - 78 lines]
> >>
> >> Can anyone see what I'm doing wrong?

Reply to this Message