April 03, 2017

MySql sample stored procedures

if else update insert stored procedure


DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `cfedapps`.`p_save_branch`(in branchId int(50),in branchCode int(5),in branchName varchar(55),in address varchar(55),in district varchar(33),in pincode bigint(10),in landline bigint(15),in startDate date,in lat long,in lon long, in branchStatus varchar(5), in branchDesc varchar(55),in userId int(4), in imgUrl varchar(55),in branchEmail varchar(55),in branchType int(5))
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
 
if (branchId is not null) then

update tbl_unit_details set firmCode = branchCode,unitName = branchName where unitId = branchId;

else

insert into tbl_unit_details (firmCode,unitName,address,district,pincode,landline,startedDate,unitLat,unitLon,status,description,userId,unitImg,branchEmailid,unittype) values (branchCode,branchName,address,district,pincode,landline,startDate,lat,lon,branchStatus,branchDesc,userId,imgUrl,branchEmail,branchType);
select last_insert_id();

end if;

    END$$

DELIMITER ;


Important notes


Setting default value of a column to CURRENT_TIMESTAMP while creating a database allows to update current date time in that field automatically

sample query
create table consumerfed (itsection varchar(32),createdDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);


Thanks

http://javabelazy.blogspot.in/

Facebook comments