How to Use DECLARE variable inside Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `DataBaseName`.`ProcedureName`$$

CREATE   PROCEDURE `ProcedureName`(IN _userid INT,OUT _retVal INT)
BEGIN
DECLARE statusflag INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
   
    START TRANSACTION;
    SET _retVal =0 ;
    select status into statusflag from TableName WHERE user_id=_userid;
    UPDATE TableName SET isblocked=1 WHERE user_id=_userid;
    COMMIT;
        SET _retVal =1 ;
    END$$

DELIMITER ;



==>now it will return dataset with column name "statusflag" with INT value.

DELIMITER $$

DROP PROCEDURE IF EXISTS `DataBaseName`.`ProcedureName`$$

CREATE  PROCEDURE `ProcedureName`(IN _code VARCHAR(100),OUT _retVal INT)
BEGIN
    DECLARE _couponcode VARCHAR(100);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
  
    START TRANSACTION;
    SET _retVal =0 ;
    SELECT MAX(coupon_code) INTO _couponcode FROM TableName WHERE coupon_code=_code ;
        IF _couponcode IS NULL THEN
    SET _retVal =0;
    ELSE
    SET _retVal =1;
    END IF;
    COMMIT;
    END$$

DELIMITER ;

Few Basic point about write procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `DataBaseName`.`ProcedureName`$$

CREATE  PROCEDURE `ProcedureName`(
    IN _companyid INT,
    OUT _retVal INT)
BEGIN
            DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
            DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
   
                              START TRANSACTION;
                                       SET _retVal =0 ;
   
                                         UPDATE TableName SET ColunmnName=Value WHERE Condition;   
                                         COMMIT;
                                      SET _retVal =1 ;
    END$$

DELIMITER ;


Delimiter is used for as procedure initating point and ending point on query editor.
DROP is used for remove procedure from stored procedure if it already there.
Create Procedure is used for to create a new Procedure with given condition.
IN is used for taking argument.
OUT is used for returning  value from procedure after execution.



and for execution :

Call ProcedureName (5);
=>Call is used for to execute that procedure and passing that argument inside procedure.

 

 NOW()  function is used for current time to insert.


select MAX(columnName) from TableName;