CODEWITHSUNDEEP
mysqlsqlstored-procedures
coolguy
coolguy

Reputation: 7954

multiple mysql stored procedure in a single call

I have a stored procedure like this and it's working fine:

$drop = $mysqli->query("DROP PROCEDURE IF EXISTS changegroup");
$initiate = $mysqli->query("
    Create Procedure changegroup(IN param1 int(10),IN param2 int(10))
    BEGIN
       UPDATE t_parts SET part_group_id = param2 WHERE part_id = param1;
    END;
");
$result = $mysqli->query("CALL changegroup($p1,$p2);");

My question is, can we put two SQL statements in a single procedure and execute the second procedure based on first like this:

BEGIN
     SELECT * FROM ........
     /**fetch the result of this mysql statment and if matches certain conditions,then execute the update statment***/

     UPDATE t_parts SET part_group_id = param2 WHERE part_id = param1;
END;

Upvotes: 0

Views: 89

Answers (1)

Krishna Rani Sahoo
Krishna Rani Sahoo

Reputation: 1539

In your stored procedure write 

if <condition> then 
  your code
end if;

So your code should be like this

Create Procedure changegroup(IN param1 int(10),IN param2 int(10))
BEGIN
     DECLARE totalcount Integer default 0;
     SELECT count(*) into totalcount  FROM yourtable where <condition>;
     /**fetch the result of this mysql statment and if matches certain conditions,then execute the update statment***/
    if(totalcount > 0) then
     UPDATE t_parts SET part_group_id = param2 WHERE part_id = param1;
    end if;
END;

Upvotes: 1

Related Questions