Problem assigning var with IF statement 1

[jgurgen]

Its telling me there is a syntax error near IF but i can't see whats wrong with it. Im going insane here. If i dont assign it to the variable its returns the correct result.


DECLARE @LoanID varchar(10),
@INV_SF decimal,
@Comm_Bonus decimal

SET @LoanID = '1234567890'
-- RETRIEVE DATA FROM DATABASE
---- Investor Servicing Fees
SET @INV_SF = IF EXISTS(SELECT * FROM FeeCBS WHERE loanid=@LoanID and (type='Inv Servicing Fee')) SELECT SUM(amount) FROM FeeCBS WHERE loanid=@LoanID and (type='Inv Servicing Fee') ELSE SELECT 0.00
---- Commision and Bonus
SET @Comm_Bonus = IF EXISTS(SELECT * FROM FeeCBS WHERE loanid=@LoanID and (type<>'Inv Servicing Fee')) SELECT SUM(amount) FROM FeeCBS WHERE loanid=@LoanID and (type<>'Inv Servicing Fee') ELSE SELECT 0.00

 

[gmmastros]

Here's how...

[COLOR=green]---- Investor Servicing Fees
[/color][COLOR=blue]IF[/color] EXISTS([COLOR=blue]SELECT[/color] * 
          [COLOR=blue]FROM[/color]   FeeCBS 
          [COLOR=blue]WHERE[/color]  loanid=@LoanID 
                 and ([COLOR=blue]type[/color]=[COLOR=red]'Inv Servicing Fee'[/color])) 
    [COLOR=blue]SELECT[/color] @INV_SF = SUM(amount) 
    [COLOR=blue]FROM[/color]   FeeCBS 
    [COLOR=blue]WHERE[/color]  loanid=@LoanID 
           and ([COLOR=blue]type[/color]=[COLOR=red]'Inv Servicing Fee'[/color]) 
[COLOR=blue]ELSE[/color] 
    [COLOR=blue]SELECT[/color] @INV_SF = 0.00

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

why not this George?

SELECT @INV_SF = COALESCE(SUM(amount),0.00)
    FROM   FeeCBS 
    WHERE  loanid=@LoanID 
           and (type='Inv Servicing Fee')

should work

use pubs

select COALESCE(SUM(convert(int,contract)),0.00)  from authors
where au_lname ='White2'


select COALESCE(SUM(convert(int,contract)),0.00)  from authors
where au_lname ='White'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gmmastros]

Denis,

You are right. That will also work. When I replied, I tried to keep the format similar to the original query so that jgurgen could see the proper way to use if exists.

I suspect that your method will be slightly faster. It's how I would have done it in my code.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom