Database of Scriptum

We assume that you have read about personnel sub system in the hospital information system that presented before at http://databasescript.blogspot.com/2012/10/entity-relationship-model-of-hospital.html
In this article will take up about inserting a user id, password and other fields that related to tUser table in personnel sub system. This procedure of inserting a user will affect to the procedure of user login at the later. Here is the syntax of creating the routine.
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertuser`(
   IN p_user_id varchar(20),
   IN p_encrypted_pass varbinary(100),
   IN p_personID varchar(18))
BEGIN
 DECLARE vMidUserId varchar(3);
 DECLARE vMidIdPegawai varchar(6);
 SET vMidUserId = MID(p_user_id,1,3);
 SET vMidIdPegawai = CONCAT(RIGHT(p_personID,2),LEFT(p_personID,4));

 INSERT INTO tuser(user_id, kata_sandi,idPegawai)
 VALUES(p_user_id,
       AES_ENCRYPT (p_encrypted_pass ,CONCAT(vMidUserId,vMidIdPegawai)),
       p_personID);
END 

One important thing in this routine is the encryption of the user password. Of course you can modify the AES_ENCRYPT option with one you like. The AES_ENCRYPT also use in the login stored procedure, and we will talk about it in the later article.