How to run multiple queries on MySQL using PROCEDURE?

Question

What's the right way to create in MySQL a PROCEDURE for multiple queries like this one made for SQL?

CREATE OR REPLACE PROCEDURE foo
IS
BEGIN
  -- The create sentence goes here. For example:
  -- EXECUTE IMMEDIATE
  -- 'CREATE TABLE bar (...)';

  -- The update sentence goes here
  -- EXECUTE IMMEDIATE
  -- 'UPDATE bar SET ...';

  -- The drop/delete sentence goes here.
  -- EXECUTE IMMEDIATE
  -- 'DROP TABLE bar;'
END;

You don't need no `execute` statements. Just the queries seperated by `;`. Don't forget to set a different separator outside the procedure like `delimiter $` — juergen d, Jun 24 '15 at 12:35
https://dev.mysql.com/doc/refman/5.0/en/create-procedure.html — Hearner, Jun 24 '15 at 12:37
@juergend I saw this reply bad voted about it (that's the reason why I'm here) http://stackoverflow.com/a/6212730/4458531 What do you think? — NineCattoRules, Jun 24 '15 at 12:43
@Simone: Don't know why. But I am not a PLSQL expert. In MySQL using just the queries is the way to go. — juergen d, Jun 24 '15 at 12:47
@juergend thanks, I don't understand the delimiter...should $ or // ...what's the difference? — NineCattoRules, Jun 24 '15 at 12:49
Asking for the "right way" will result in an opinion. Are you looking for the MySQL recommended syntax for a `PROCEDURE`? — Nathan, Jun 24 '15 at 13:01
@Nathan I'm looking for the best option to run multiple queries at once in MySQL — NineCattoRules, Jun 24 '15 at 14:19

score 5 · Accepted Answer · answered Jun 24 '15 at 12:52

In MySQL just insert the 3 queries into your procedure. You need a different delimiter defined before, because otherwise the engine will terminate the procedure definition at the first ; which would make it incomplete. So change to anything else like $ or //

DELIMITER //
CREATE PROCEDURE your_proc()
BEGIN
  create ... ;
  update ... ;
  drop ... ;
END //
DELIMITER ;

score 0 · Answer 2 · answered Jun 24 '15 at 12:42

0

CREATE PROCEDURE foo 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 
    QUERY1; -- SELECT or whatever
    QUERY2;
GO

answered Jun 24 '15 at 12:42

Hearner

2,711
3
17
34

and maybe you should explain the final GO – NineCattoRules Jun 24 '15 at 12:52
2

SQL-Server uses `go`, MySQL not. – juergen d Jun 24 '15 at 14:17

How to run multiple queries on MySQL using PROCEDURE?

2 Answers2