CODEWITHSUNDEEP
phpmysqlsqlstored-procedures
Groot
Groot

Reputation: 31

MySql stored procedure validation

I need a stored procedure to perform an action according to a condition that is saved in a PHP variable. This stored procedure would run one of possible 2 insert operations:

if $var = 1 then insert 1 else insert 2

Is this possible or will I have to do 2 stored procedures and do the validation in the php script?

Example:

DELIMITER $$
CREATE PROCEDURE example_sp 
    (in val1 char(100), in val2 char(50), in val3 char(10), in val4 char(50))
BEGIN
      IF($var = 1) 
      THEN
      INSERT INTO table1 (val1, val2) VALUES (val1, val2);
      ELSE
      INSERT INTO table2 (val1, val2) VALUES (val1, val2);    
      END IF;    
END $$
DELIMITER ;

Upvotes: 1

Views: 679

Answers (1)

nbk
nbk

Reputation: 49395

No you need only one stored procedure.

but for this you need dynamic sql

DELIMITER $$
   CREATE PROCEDURE example_sp 
       (IN var int, in val1 char(100), in val2 char(50), in val3 char(10), in val4 char(50))
   BEGIN
       IF(var = 1) 
       THEN
           SET @sql := CONCAT("INSERT INTO table1 (",val1,",", val2,") VALUES (?,?);");
           SET @a := val1;
           SET @b := val2;
       ELSE
           SET @sql := CONCAT("INSERT INTO table2 (",val1,",", val2,") VALUES (?,?);");
            SET @a := val1;
           SET @b := val2;
       END IF;    
       
       PREPARE stmt FROM @sql;
       EXECUTE stmt USING @a,@b;
       DEALLOCATE PREPARE stmt;
   END $$
DELIMITER ;

And in PHP

$stmt=$db->prepare("CALL example_sp(?,?,?,?,?)");
$stmt->bind_param('issss',$var,$val1,$val2,$val3,$val4);
$stmt->execute();

Upvotes: 1

Related Questions