Execute triggers stored procedures on SqlFiddle. Mysql

Question

Does SQL-fiddle facilitate execution of triggers/stored procedures?

I have been unable to execute even the simplest form of stored procedure on sqlfiddle

DELIMITER $$
DROP PROCEDURE IF EXISTS myProc $$

CREATE PROCEDURE myProc()
BEGIN

END$$
DELIMITER ;

Sqlfiddle does not allow executing this(above) sql in build schema, but allows create table etc

Note: The same syntax is working for me on my localhost using wamp with mysql 5.5.24

Can anyone guide please?

Warfourd

Answer 1

Instead of using the delimiter option (which is not a real SQL statement, but rather only a command for the mysql command prompt) use the "Query Terminator" option on SQL Fiddle to establish your delimiter.

For example:

http://sqlfiddle.com/#!2/88fcf

Note the // dropdown below the schema box? That's the SQL Fiddle equivalent to the mysql DELIMITER command.

Longer example with queries in the stored procedure (note that within the stored procedure, ; is still used as a delimiter):

http://sqlfiddle.com/#!9/4db78

Full disclosure: I'm the author of SQL Fiddle.

Studbook

@JakeFeasel: let me take this opportunity to thank you once more for the AWESOME SQL Fiddle. You're the man! :| Keep rocking... – Nne

@LenielMacaferi lol Thanks! Glad you like the site. Keep up the good work on the SQL questions! – Studbook

See also this bigger example, which shows that the queries IN the procedure actually need ; to delimit. – Tattoo

@Tattoo looks like using MySQL 5.6 it stop to work and showing an error: DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel . I'm using the example you sent sqlfiddle.com/#!2/e5a27/3 . there is another thread in Portuguese about it too pt.#380529 – Gland

Answer 2

I couldn't get this answer to work on sql fiddle, but found db-fiddle, and it seems to work.

Example in DB Fiddle

If the above doesn't work for some reason, do the following

• Go here: https://www.db-fiddle.com/

• Enter the following SQL on the left, and SELECT * FROM tblTest; on the right.

• Select "MySql 5.7" or whatever in dropdown.

• Click "Run"

   DELIMITER //
  
   CREATE TABLE tblTest (col1 INT)//
  
   INSERT INTO tblTest VALUES (9)//
  
   CREATE PROCEDURE dowhile() 
   BEGIN   
     DECLARE v1 INT DEFAULT 3;
  
     WHILE v1 > 0 DO
       INSERT INTO tblTest VALUES(v1);
       SET v1 = v1 - 1;   
     END WHILE; 
   END//
  
   INSERT INTO tblTest VALUES (8)//
  
   select * from tblTest// 
   call dowhile()// 
   select * from tblTest// 
  
   DELIMITER ;
  

Flatwise

Can you keep it simple by defining delimiter right before CREATE PROCEDURE dowhile() line like DELIMITER // CREATE PROCEDURE dowhile() and end it right after procdure definition completes like END// DELIMITER ; – Warfourd

You will able to use normal ; in all other statements except the create procedure statement – Warfourd

Hi, I have this one and it errors out, db-fiddle.com/f/7iPdu4Hdzs7S8dAHeRN6TT/4 – Merta