Question 245806 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql: Conditionally Create a stored procedure

How would I conditionally create a stored procedure in MySQL? I only want the stored procedure created if another condition is true.

SELECT COUNT(*)
INTO @version
FROM version
WHERE version = '9';


IF (@version) THEN
    drop PROCEDURE if exists `SP_Test`;
    
    DELIMITER $$
    CREATE PROCEDURE `SP_Test`(
                    IN `field1` VARCHAR(20),
                    INOUT `fieldOut` varchar(1024))
        COMMENT 'Test Stored Procedure'
    BEGIN 
    
        -- Stored Procedure Logic 
    
    END$$
    DELIMITER ;

    UPDATE `version` 
    SET `date`= NOW(), `version` = '10'
    WHERE  `version`    = '9'
      
    SELECT "DB Updated";
ELSE
    SELECT "version does not match";
END IF;
Tags:

2

Answers


  1. 0

    Unfortunayely, I don’t think thus is possible purely within mysql.

    You can only use conditional statements within stored routines (procedure, function, trigger, event) in mysql, so you woukd need a stored procedure in this case.

    There are quite a lot of restrictions as to what sql statements you can use in stored procs, see mysql manual.

    As drop/create procedure statements not listed within the allowed statements, you cannot dynamically drop and create a stored proc from another stored proc. Seems to be a reasonable restriction, to be honest.

    Login or Signup to reply.
  2. 0

    Regarding pure sql, the closest I can think of is create an OS-level procedure-making text file (i.e a sql script) using statements such as SELECT INTO OUTFILE within the procedure. And leave a hint at the end to remind the user to run the necessary source statement afterwards, which in turn creates a procedure . Here is an example :

    delimiter //
drop procedure if exists make_sp_txt//

create procedure  make_sp_txt()
if datediff(current_date(),'2000-01-01')>=0 then
select concat("

delimiter // 
drop procedure if exists testsp// 

create procedure  testsp()
begin
select 'hello millennium' as banter;

end//

delimiter ;") into outfile "d:/testsp.sql";

select 'please run "source d:/testsp.sql"' as hint;
end if;

end //


delimiter ;

    Let’s call the main procedure.

     call make_sp_txt;
+-----------------------------------+
| hint                              |
+-----------------------------------+
| please run "source d:/testsp.sql" |
+-----------------------------------+

    The text file d:/testsp.sql created in the procedure looks like this in the notepad:

    

delimiter // 
drop procedure if exists testsp//

create procedure  testsp()
begin
select 'hello millennium' as banter;

end//

delimiter ;

    Note: A backslash character is in position for each line break. At the end of the statement which declares double slash as the delimiter, I intentionally added a whitespace at the end, otherwise an error will be raised as the back slash character generated at the end will be counted as part of the delimiter, which is illegal.
    Now we run the source statement to create the second procedure.

     source d:/testsp.sql;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

    A procedure named testsp has been created through the script. Finally, we can test it:

    call testsp;
+------------------+
| banter           |
+------------------+
| hello millennium |
+------------------+

    To conclude, it’s possible to INDIRECTLY create a procedure using another procedure. Here are the pros and cons.

    Pros
    It does support condition. That’s the only good point.

    Cons
    The process of relying on string functions to write a full-blown procedure-making script can be painful. Not to mention escape characters and other trivias which can mess up the contents of the script.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search