Multiple insert queries in one stored procedure My Sql

Question

I had to create many routines (stored procedures) for every query, how can I do the following procedures into one single procedure, same way I need to put around eight procedures like that, any idea could be helpful, thanks in advance.

Procedure 1

INSERT INTO public_holidays (user_id, department_id,designation_id,date_cur,clock_in,clock_out)
SELECT cl.user_id, des.department_id , us.designation_id, cl.date,cl.clock_in, cl.clock_out
FROM clock cl 
INNER JOIN holidays AS hol ON hol.date = cl.date
INNER JOIN users AS us ON cl.user_id = us.id
INNER JOIN designations AS des ON des.id = us.designation_id
WHERE date(cl.created_at) = cur_dat
AND TIMESTAMPDIFF(second,cl.clock_in, cl.clock_out) = 28800;

Procedure 2

INSERT INTO public_holidays_nine (user_id, department_id,designation_id,date_cur,clock_in,clock_out)
SELECT clo.user_id, design.department_id , uses.designation_id, clo.date,clo.clock_in, clo.clock_out 
FROM clock clo
INNER JOIN holidays AS holl ON holl.date = clo.date
INNER JOIN users AS uses ON clo.user_id = uses.id
INNER JOIN designations AS design ON design.id = uses.designation_id
WHERE date(clo.created_at) = cur_dat
AND TIMESTAMPDIFF(second,clo.clock_in, clo.clock_out) = 32400;

You can use Mode like @Mode as a parameter in procedure. and pass it as 1,2,3,... From your code behind. Your condition inside procedure will be If @Mode=1 Begin query1 End same for all modes. — Rahul Hendawe
– Rahul Hendawe, Commented Jun 14, 2016 at 5:41
maybe one problem is that you have tables such as public_holidays and public_holidays_nine when you should have less, like 1, not 10 (at least perhaps) for those — Drew
– Drew, Commented Jun 14, 2016 at 20:32
Maybe the reason you have so many sp's is because you have too many tables and you shouldn't have too many table. Just maybe. — Drew
– Drew, Commented Jun 15, 2016 at 4:54

Wasiq Muhammad · Accepted Answer · 2016-06-14 07:02:20Z

1

Try this

CREATE PROCEDURE `sp_test`(IN _date datetime)
BEGIN
    #Routine body goes here...
INSERT INTO public_holidays (user_id, department_id,designation_id,date_cur,clock_in,clock_out)
SELECT cl.user_id, des.department_id , us.designation_id, cl.date,cl.clock_in, cl.clock_out
FROM clock cl 
INNER JOIN holidays AS hol ON hol.date = cl.date
INNER JOIN users AS us ON cl.user_id = us.id
INNER JOIN designations AS des ON des.id = us.designation_id
WHERE date(cl.created_at) = cur_dat
AND TIMESTAMPDIFF(second,cl.clock_in, cl.clock_out) = 28800;


INSERT INTO public_holidays_nine (user_id, department_id,designation_id,date_cur,clock_in,clock_out)
SELECT clo.user_id, design.department_id , uses.designation_id, clo.date,clo.clock_in, clo.clock_out 
FROM clock clo
INNER JOIN holidays AS holl ON holl.date = clo.date
INNER JOIN users AS uses ON clo.user_id = uses.id
INNER JOIN designations AS design ON design.id = uses.designation_id
WHERE date(clo.created_at) = cur_dat
AND TIMESTAMPDIFF(second,clo.clock_in, clo.clock_out) = 32400;

END

You can Call this Procedure as

CALL sp_test(param1)

edited Jun 14, 2016 at 7:02

answered Jun 14, 2016 at 5:42

Wasiq Muhammad

3,1483 gold badges19 silver badges30 bronze badges

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

8 Comments

Crysis Over a year ago

it shows the following error while creating, The following query has failed: "CREATE DEFINER=root@localhost PROCEDURE pros(IN cur_dat VARCHAR(40)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER INSERT INTO public_holidays. And having only one parameter cur_dat.

Wasiq Muhammad Over a year ago

From where you add this procedure? by running script or manually ? what is your dbengine

Crysis Over a year ago

by running a script from laravel, DB::statement(DB::raw('CALL public_holidays(?)'), array($current_date)); my dbengine is InnoDB.

Wasiq Muhammad Over a year ago

Here you can passing only one parameter as it expects 6 please check and verify by running procedure in phpmyadmin

Crysis Over a year ago

i need to send only one parameter and one parameter works well with one procedure.

|

Collectives™ on Stack Overflow

Multiple insert queries in one stored procedure My Sql

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related