USE `gateguard`;

DROP PROCEDURE IF EXISTS `sp_createUsers`;

DELIMITER $$
CREATE PROCEDURE `sp_createUsers` (
    IN p_USERNAME VARCHAR(255),
    IN p_FIRST_NAME VARCHAR(255),
	IN p_LAST_NAME VARCHAR(255),
    IN p_PASSWORD VARCHAR(255),
    IN p_EMAIL VARCHAR(255),
    IN p_MOBILE_NUMBER VARCHAR(255),
    IN p_MODIFIED_BY INT,
    OUT v_USER_ID INT,
	OUT v_ERROR_CODE VARCHAR(255),
	OUT v_ERROR_MESSAGE VARCHAR(255)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
    BEGIN
         GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
		SET v_ERROR_MESSAGE = @text;
		SET v_ERROR_CODE = CONCAT(@errno, '|', @sqlstate);
		ROLLBACK;
    END;

    SET AUTOCOMMIT=0;
    START TRANSACTION;

    SET v_USER_ID = LAST_INSERT_ID();

    COMMIT;
END$$
DELIMITER ;
