How to call a stored procedure in an if statement?

Question

I have SP in MySQL that in one of its row calls another SP in if statement like below:

IF ((CALL SP_CheckExist(id1,id2)) THEN //some thing ; END IF;

SP_CheckExist retuern true or false. But I have an error in syntax!

SP_CheckExist is like this:

BEGIN
    IF EXISTS(SELECT 1 FROM tbl1 WHERE Id1 = p_Id1 AND Id2 = p_Id2 LIMIT 1)
    THEN SELECT TRUE;
    ELSE SELECT FALSE;
    END IF; 
END

Actually even I cant do some thing like this:

SET result = (CALL SP_CheckExist(1,1));

Why?

Error messages are relevant. They are not there to annoy. Please read them! Whatever, how comes you've written a stored procedure that *returns* something? Functions return stuff, procedures don't. — Álvaro González, Dec 19 '12 at 08:48
SP_CheckExist should be a function, not a procedure. procedures don't return values. you can use an out parameter with procedures. — , Dec 19 '12 at 08:50
I guess you should use an user defined function or assign SP return value to defined variable before IF statement. — valex, Dec 19 '12 at 08:50
BEGIN IF EXISTS(SELECT 1 FROM tbl1 WHERE Id1 = p_Id1 AND Id2 = p_Id2 LIMIT 1) THEN SELECT TRUE; ELSE SELECT FALSE; END IF; END — Fatemeh Gharri, Dec 19 '12 at 08:51
@Highlan - Please note that you can **edit** the question. Code in comments is unreadable. And you still haven't posted the error message. — Álvaro González, Dec 19 '12 at 08:52
possible duplicate of [differences between procedure and function and routine?](http://stackoverflow.com/questions/2680745/differences-between-procedure-and-function-and-routine) — Álvaro González, Dec 19 '12 at 08:56

Saharsh Shah · Answer 1 · 2012-12-19T09:06:39.367

Either you have to create a function to check the existance of data or You have to use OUT type parameter in procedure and use that variable in IF condition.

Function

DELIMITER $$

DROP FUNCTION IF EXISTS `fn_CheckExist`$$

CREATE FUNCTION `fn_CheckExist`(p_id1 INT, p_id2 INT) RETURNS BOOL CHARSET latin1
    READS SQL DATA
BEGIN
    DECLARE _result BOOL DEFAULT FALSE;
    IF EXISTS(SELECT 1 FROM tbl1 WHERE Id1 = p_Id1 AND Id2 = p_Id2 LIMIT 1)
      THEN SET _result = TRUE;
      ELSE SET _result = FALSE;
    RETURN _result;
END$$

DELIMITER ;

SP

DECLARE _result BOOL DEFAULT FALSE;
SELECT fn_CheckExist(id1, id2) INTO _result;
IF(_result) THEN 
    /* Your Code Snippet*/
ELSE
    /* Your Code Snippet*/
END IF;

OR Through Procedure

CREATE PROCEDURE `sp_CheckExist`(IN p_Id1 INT, IN p_Id2 INT, OUT _result BOOL)
BEGIN
    IF EXISTS(SELECT 1 FROM tbl1 WHERE Id1 = p_Id1 AND Id2 = p_Id2 LIMIT 1)
    THEN SET _result = TRUE;
    ELSE SET _result = FALSE;
    END IF; 
END

2nd SP

DECLARE _result BOOL DEFAULT FALSE;
CALL SP_CheckExist(id1,id2,_result);
if(_result) then 
   /* Your Code Snippet*/
else
   /* Your Code Snippet*/
end if;

How to call a stored procedure in an if statement?

1 Answers1