MySql stored Procedures with multiple statements


Without Parameter
delimiter ;;
drop procedure if exists usp_Income_updateApproved;;
create procedure usp_Income_updateApproved()
begin
START TRANSACTION;
Insert into `backup_income`
SELECT incitem_id, person, date, amount, comments, user_id, approved, date_inserted
FROM `m_income`
WHERE id
IN (
SELECT DISTINCT id
FROM unapproved_income
)
AND approved =1;

update m_income orig, unapproved_mahallu_income unappr  SET
orig.incitem_id = unappr.incitem_id,
orig.person = unappr.person,
orig.date = unappr.date,
orig.amount = unappr.amount,
orig.amount = unappr.comments
Where orig.id = unappr.id and orig.approved = 1;
Update `mahallu_income` SET approved = 1
where id
IN (
SELECT DISTINCT id FROM unapproved_income
);

Delete from unapproved_income;
COMMIT;

end
;;


With Parameter
delimiter ;;
drop procedure if exists usp_Income_updateApproved;;
create procedure usp_Income_updateApproved(IN userID INT)
begin
START TRANSACTION;
Insert into `backup_income`
SELECT incitem_id, person, date, amount, comments, user_id, approved, date_inserted
FROM `m_income`

WHERE id
IN (
SELECT DISTINCT id
FROM unapproved_income
)
AND approved =1;

update mincome orig, unapproved_income unappr  SET 
orig.incitem_id = unappr.incitem_id,
orig.person = unappr.person,
orig.date = unappr.date,
orig.amount = unappr.amount,
orig.comments = unappr.comments,
orig.user_id = userID,
orig.approved = 1
Where orig.id = unappr.id;

Delete from unapproved_income;

COMMIT;
end
;;

How to view definition of a stored procedure?
It is stored in ROUTINES table under database INFORMATION_SCHEMA .

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

No comments:

Post a Comment