Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- zone_id(PK) ZoneName
- ----------- --------
- 1 east
- 2 west
- 3 north
- 4 south
- tbluser
- usrId(PK) userzoneId(FK to tblzone) username
- -------- ------------------------- ------------
- 1 1 manish
- 2 3 rahul
- 3 2 ankit
- 4 4 amir
- 5 2 rashmi
- 6 1 akash
- tbldoctor
- docId(PK) usrId(Fk to tbluser) docname
- -------- -------------------- ------------
- 1 2 hemant
- 2 2 chintu
- 3 3 rahim
- 4 1 salman
- 5 3 kishor
- 6 3 saurabh
- 7 2 banti
- tblmedAssign
- transId(Pk) doctorId(FK to tbldoctor) medId(FK) dateInsert
- ---------- ------------------------- ------ -----------
- 1 2 2 20/12/2012
- 2 3 3 21/12/2012
- 3 2 3 23/12/2012
- 4 4 1 24/12/2012
- tblunnormalize
- transId(Pk) docname username ZoneName dateInsert
- ------------- ------- -------- -------- ----------
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `medAssignProcedure`$$
- CREATE
- PROCEDURE medAssignProcedure(IN transId INT, IN doctorId INT, IN medId INT )
- BEGIN
- DECLARE doctorname VARCHAR(100);
- DECLARE user_Name VARCHAR(100);
- DECLARE zone_name VARCHAR(100);
- DECLARE userId INT;
- DECLARE datetimenow DATETIME;
- SET datetimenow = NOW();
- INSERT INTO tblmedassign VALUES (transId,doctorId,medId,datetimenow);
- SET doctorname = (SELECT docname FROM tbldoctor WHERE docid = doctorId);
- SET userId = (SELECT usrid FROM tbldoctor WHERE docid = doctorId);
- SET user_Name = (SELECT username FROM tbluser WHERE usrid = userId);
- SET zone_name = (SELECT zonename FROM tblzone WHERE zone_id = (SELECT userzoneId FROM tbluser WHERE usrid = userId));
- INSERT INTO tblunnormalize VALUES (transId, doctorname, user_Name, zone_name, datetimenow);
- END$$
- DELIMITER ;
Add Comment
Please, Sign In to add comment