CODEWITHSUNDEEP
mysqlstored-proceduresinsert
Crysis
Crysis

Reputation: 418

Multiple insert queries in one stored procedure My Sql

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;

Upvotes: 1

Views: 2632

Answers (1)

Wasiq Muhammad
Wasiq Muhammad

Reputation: 3118

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)

Upvotes: 1

Related Questions