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
– juergen d, Commented Jun 24, 2015 at 12:35
@juergend I saw this reply bad voted about it (that's the reason why I'm here) stackoverflow.com/a/6212730/4458531 What do you think? — NineCattoRules
– NineCattoRules, Commented Jun 24, 2015 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
– juergen d, Commented Jun 24, 2015 at 12:47
@juergend thanks, I don't understand the delimiter...should $ or // ...what's the difference? — NineCattoRules
– NineCattoRules, Commented Jun 24, 2015 at 12:49

juergen d · Accepted Answer · 2015-06-24 12:52:12Z

5

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 ;

answered Jun 24, 2015 at 12:52

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hearner · Accepted Answer · 2015-06-24 12:42:54Z

0

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

answered Jun 24, 2015 at 12:42

Hearner

2,7053 gold badges19 silver badges34 bronze badges

2 Comments

NineCattoRules Over a year ago

and maybe you should explain the final GO

juergen d Over a year ago

SQL-Server uses go, MySQL not.

Collectives™ on Stack Overflow

How to run multiple queries on MySQL using PROCEDURE?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related