MySQL-StoreProcedures-things.txt

* If we are using 'IF & ELSEIF', then make sure there should be no space B/W ELSE & IF.
* Always place ';' after 'END IF'
* 




* Deleting multiple records using IN clause ------------------------------------------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_user_allergies_upsert` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_user_allergies_upsert`(IN IN_user_id INT, IN IN_relative_id INT,IN IN_commaSeparatedAllergy_ids VARCHAR(2000))

BEGIN

    set @sql = concat("DELETE FROM ehr_allergy WHERE user_id = ",IN_user_id," AND relative_id = ",IN_relative_id," AND allergy_id IN ('", replace(IN_commaSeparatedAllergy_ids,",","','"), "')");

    PREPARE q FROM @sql;
    
    execute q;

COMMIT;

END $$

DELIMITER ;

*******************************************************************************************************
* IF condition  ----------------------------------------------------------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_user_file_upsert` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_user_file_upsert`(IN IN_user_id INT, IN IN_relative_id INT, IN IN_file_id INT,IN IN_file_type_id INT, IN IN_photo BLOB)
BEGIN

  IF (IN_file_id = 0) THEN

    UPDATE ehr_file SET relative_id = IN_relative_id, file_type_id = IN_file_type_id, file_blob =IN_photo, updated_at=now()
     WHERE file_id = IN_file_id;

  ELSE

    INSERT INTO ehr_file (user_id, relative_id, file_type_id, file_blob, created_by, updated_by, file_date, upload_date,
    created_at, updated_at)
    VALUES (IN_user_id, IN_relative_id, IN_file_type_id,IN_photo,IN_user_id, IN_user_id, now(), now(), now(), now());

  END IF;

COMMIT;

END $$

DELIMITER ;

*******************************************************************************************************
* Temparary variable with IF & ELSEIF in Sp -----------------------------------------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS `homeotel`.`sp_user_medical_history_upsert` $$
CREATE PROCEDURE `homeotel`.`sp_user_medical_history_upsert` (IN IN_medical_history_name VARCHAR(255), IN IN_user_id INT,
                                               IN IN_relative_id INT, IN IN_commaSeparatedAllergy_ids VARCHAR(2000),
                                               IN IN_commaSeparatedAllergyObject VARCHAR(2000))
BEGIN

DECLARE Var_table_name VARCHAR(30);
DECLARE Var_id_field_name VARCHAR(30);


DECLARE exit handler for sqlexception
  BEGIN

    GET DIAGNOSTICS CONDITION 1
    @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
    SELECT @p1 as error_code  , @p2 as error;
    ROLLBACK;

END;

DECLARE exit handler for sqlwarning
 BEGIN

    GET DIAGNOSTICS CONDITION 1
    @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
    SELECT @p1 as error_code  , @p2 as error;
    ROLLBACK;

END;

START TRANSACTION;

    IF(IN_medical_history_name = 'allergies') THEN

     SET Var_table_name = 'ehr_allergy';
     SET Var_id_field_name = 'allergy_id';

    ELSEIF(IN_medical_history_name = 'currentMedication') THEN

     SET Var_table_name = 'ehr_current_medication';
     SET Var_id_field_name = 'medication_id';

    ELSEIF(IN_medical_history_name = 'postMedication') THEN

     SET Var_table_name = 'ehr_post_medication';
     SET Var_id_field_name = 'medication_id';

    ELSEIF(IN_medical_history_name = 'surgeries') THEN

     SET Var_table_name = 'ehr_surgery';
     SET Var_id_field_name = 'surgery_id';

    ELSEIF(IN_medical_history_name = 'injuries') THEN

     SET Var_table_name = 'ehr_injury';
     SET Var_id_field_name = 'injury_id';

    ELSEIF(IN_medical_history_name = 'chronics') THEN

     SET Var_table_name = 'ehr_chronic';
     SET Var_id_field_name = 'disease_id';

    END IF;

    set @sql = concat("DELETE FROM ",Var_table_name,"
                          WHERE user_id = ",IN_user_id," AND relative_id = ",IN_relative_id);
    PREPARE q FROM @sql;
    execute q;

    SET @save_allergies_query = CONCAT("INSERT INTO ",Var_table_name,"
                            (user_id, relative_id, ",Var_id_field_name,", created_by, updated_by,created_at, updated_at)
                            VALUES ",replace(IN_commaSeparatedAllergyObject,"timestamp",now()));
    PREPARE save_allergies_query FROM @save_allergies_query;
    EXECUTE save_allergies_query;

COMMIT;

END $$

DELIMITER ;

*******************************************************************************************************
* Temparary variable dynamic storage with IF & ELSEIF in Sp -------------------------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_user_lifestyle_upsert` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_user_lifestyle_upsert`(IN IN_user_id INT, IN IN_relative_id INT,
                           IN IN_smoking_id INT, IN IN_alcohol_id INT, IN IN_excercise_id INT, IN IN_activity_level_id INT,
                           IN IN_profession_id INT, IN IN_food_id INT, IN IN_heat_id INT)
BEGIN

DECLARE lifestyleCount INT;

DECLARE exit handler for sqlexception
  BEGIN

    GET DIAGNOSTICS CONDITION 1
    @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
    SELECT @p1 as error_code  , @p2 as error;
    ROLLBACK;

END;

DECLARE exit handler for sqlwarning
 BEGIN

    GET DIAGNOSTICS CONDITION 1
    @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
    SELECT @p1 as error_code  , @p2 as error;
    ROLLBACK;

END;

START TRANSACTION;

  SET lifestyleCount = (SELECT COUNT(*) FROM ehr_lifestyle WHERE user_id = IN_user_id AND relative_id = IN_relative_id);

  IF (lifestyleCount > 0) THEN

    UPDATE ehr_lifestyle SET smoking_id = IN_smoking_id, alcohol_id = IN_alcohol_id, excercise_id = IN_excercise_id,
      activity_level_id = IN_activity_level_id, profession_id = IN_profession_id, food_id = IN_food_id,
      heat_id = IN_heat_id, updated_at=now()
     WHERE user_id = IN_user_id AND relative_id = IN_relative_id;

  ELSE

    INSERT INTO ehr_lifestyle (user_id, relative_id, smoking_id, alcohol_id, excercise_id, activity_level_id, profession_id,
               food_id, heat_id, created_by, updated_by, created_at, updated_at)
               VALUES (IN_user_id, IN_relative_id, IN_smoking_id,IN_alcohol_id,IN_excercise_id, IN_activity_level_id,
               IN_profession_id, IN_food_id, IN_heat_id, user_id, user_id, now(), now());

  END IF;

COMMIT;

END $$

DELIMITER ;

*******************************************************************************************************
*