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";
