How to use IF...ELSE sql statement with multiple selects?

Question

Simple academic project. Simple procedure. If there is something already in Payments table - then take debt value from that table, if there are no values - take it from Tariff table. But why such conidtion doesn't work?

ALTER PROCEDURE dbo.GetDebt
    (
    @LoanId int
    )
AS

    IF NOT EXISTS (SELECT top 1 * FROM Payment WHERE LoanId = @LoanId) 
    BEGIN
        SELECT (TotalLoan + ( ( TotalLoan / 100 ) * Interest))  as Debt FROM Loan L, Tariff T
        WHERE L.TariffIf = L.TariffId
    END
    ELSE
    BEGIN
        SELECT MIN(Debt) as Debt FROM Loan L 
        RIGHT OUTER JOIN Payment P -- Joins are cool.
        ON L.LoanId = P.LoanId
        WHERE P.LoanId = @LoanId 
    END

You forgot to add ON clause in right join.

Nikola Markovinović
– Nikola Markovinović

2012-12-19 22:40:04 +00:00
Commented Dec 19, 2012 at 22:40 — Nikola Markovinović
– Nikola Markovinović, Commented Dec 19, 2012 at 22:40
Also, you forgot to never use right join.

Michael Fredrickson
– Michael Fredrickson

2012-12-19 22:40:51 +00:00
Commented Dec 19, 2012 at 22:40 — Michael Fredrickson
– Michael Fredrickson, Commented Dec 19, 2012 at 22:40

DiverseAndRemote.com · Accepted Answer · 2012-12-19 22:46:28Z

4

use BEGIN and END around your statements like so:

IF (SELECT count(*) FROM Payment WHERE LoanId = @LoanId) = 0 
BEGIN

    SELECT (TotalLoan + ( ( TotalLoan / 100 ) * Interest))  as Debt FROM Loan L 
    RIGHT OUTER JOIN Tariff -- TODO: Add correct ON clause here
    WHERE L.LoanId = @LoanId

END
ELSE
BEGIN
    SELECT MIN(Debt) as Debt FROM Loan L 
    RIGHT OUTER JOIN Payment P -- Joins are cool.
    ON L.LoanId = P.LoanId
    WHERE P.LoanId = @LoanId 
END

Also note that you are missing an on clause for your right outer joins which will cause errors.

Also it might be more efficient to change

IF (SELECT count(*) FROM Payment WHERE LoanId = @LoanId) = 0

to

IF NOT EXISTS (SELECT * FROM Payment WHERE LoanId = @LoanId)

The keyword being "might"

edited Dec 19, 2012 at 22:46

answered Dec 19, 2012 at 22:33

DiverseAndRemote.com

20k10 gold badges65 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dracontis Over a year ago

This isn't working. I tried this, before posting this question. Still error at WHERE.

DiverseAndRemote.com Over a year ago

Dude, I copied your sql without really looking it at. Your sql is not working to begin with because you are missing the on and its expressions after your right outer join

Dracontis Over a year ago

Oh... That's sad that I didn't notice this.

Joel Coehoorn · Accepted Answer · 2012-12-19 22:46:34Z

3

If/Else is almost always the entirely wrong approach for sql code. It's hard to give you an exact example without knowing more about your tables, but you really want something more like this:

SELECT COALESCE(P.Debt, TotalLoan + ( ( TotalLoan / 100 ) * Interest))  as Debt
FROM Loan L
LEFT JOIN Tariff T ON T.LoanID = L.LoanID
LEFT JOIN (SELECT LoanID, Min(Debt) As Debt FROM Payment GROUP BY LoanID) P
WHERE L.LoanID = @LoanID

No If/Else required.

answered Dec 19, 2012 at 22:46

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

1 Comment

Dracontis Over a year ago

As for description of COALESCE, I'll analyze how it works and will use it. Looking better, then IF..ELSE statements.

Collectives™ on Stack Overflow

How to use IF...ELSE sql statement with multiple selects?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related