CODEWITHSUNDEEP
sqlt-sql
Dracontis
Dracontis

Reputation: 4364

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

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

Upvotes: 0

Views: 11294

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415690

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.

Upvotes: 3

DiverseAndRemote.com
DiverseAndRemote.com

Reputation: 19888

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"

Upvotes: 4

Related Questions