SQL Syntax error near variable in IF statement

Question

I'm new in MySQL and I'm getting:

SQL syntax error near 'IsTrue=1 THEN' of the IF statement

The MySQL function is like :

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `GetABCD`(Var1 int, Var2 int,Var3 Int, IsTrue int) RETURNS decimal(24,14)
Return (

IF IsTrue=1 THEN

Select  sum(A) E from  e_pnfi 
where ID = (select ID from Main where  CenterID=Var1 
 and YourID=(Select YourID from year Where Year=Var2)) and res in (select highid from industry where mediumid=Var3)

ELSE

Select  sum(A) E from  e_pnfi 
where ID = (select ID from Main where  CenterID=Var1 
 and YourID=(Select YourID from year Where Year=Var2)) and res in (select highid from industry where highid=Var3)

END IF;

)

Where am I getting wrong? Can anyone help plz?

John Woo John Woo · Accepted Answer · 2013-03-15T13:01:36

use BEGIN and END statement,

DELIMITER $$
CREATE FUNCTION `GetABCD`(Var1 int, Var2 int,Var3 Int, IsTrue int) 
RETURNS decimal(24,14)
BEGIN

    DECLARE retVal DECIMAL(24,14);
    IF IsTrue=1 THEN

        SET retVal = 
            (
            SELECT  SUM(A) E 
            FROM    e_pnfi 
            WHERE   ID IN (SELECT ID FROM Main WHERE  CenterID = Var1) AND 
                    YourID IN (SELECT YourID FROM year WHERE Year = Var2) AND 
                    res IN (SELECT highid FROM industry WHERE mediumid = Var3)
        );

    ELSE

        SET retVal =
        (
            SELECT  sum(A) E 
            FROM  e_pnfi 
            WHERE   ID IN (SELECT ID FROM Main WHERE  CenterID = Var1) AND 
                    YourID IN (SELECT YourID FROM year WHERE Year = Var2) AND 
                    res in (SELECT highid FROM industry WHERE highid = Var3)
        );

    END IF;

    RETURN retVal;

END $$
DELIMITER ;

SQL Syntax error near variable in IF statement

1 Answers