Create stored procedure for insert and update operation in mysql/sql

Benifit and Advantage of stored procedure :

Stored procedure is single time complied when you creating it first time. It means if you are using stored procedure then you don't need to compile every time. So it saving the compile time.
Its increase the execution time of your query.
If error is comes in your query then its roll back your previous statement.

Creating My First Stored Procedure :

I am very coragious to learn new thing. So today i started to use stored procedure and below is my first simple stored procedure which i creted to perform insert and update operation. Its easy to use stored procedure. Because you don't need to run same query again and again. It reduce the execution time of your site.
See example -

create procedure ADD_PRODUCT(
    IN ID INT(20),
    IN PRODUCT_NAME VARCHAR(255),
    IN PRODUCT_SKU VARCHAR(20),
    IN PRODUCT_FILE VARCHAR(255),
    IN IS_ACTIVE TINYINT(1),
    IN IS_PRINTABLE TINYINT(1)
)
BEGIN
 DECLARE lastInsertid int default 0;
 IF ID = 0 THEN
  INSERT INTO products(product_name, PRODUCT_DESCRIPTION, product_sku, product_image, is_active, is_printable)
  VALUES(PRODUCT_NAME, PRODUCT_DESCRIPTION, PRODUCT_SKU, PRODUCT_FILE, IS_ACTIVE, IS_PRINTABLE);
  SET lastInsertid = LAST_INSERT_ID();
 ELSE
  UPDATE products SET
   product_name= PRODUCT_NAME,
   product_sku= PRODUCT_SKU,
   product_image= PRODUCT_FILE,
   is_active= IS_ACTIVE,
   is_printable= IS_PRINTABLE
  WHERE id = ID;
 END IF;

 SELECT lastInsertid as "INSERTED_ID";
END;



If you like this post please don't forgot to leave a comment.

Chears ..
Happy Coding :)