Mysql simple stored procedure example

A stored function is a special kind stored program. 
You use stored functions to encapsulate common formulas or business rules that are reusable among SQL statements or stored programs.

Examples 1:

CREATE PROCEDURE Country
(IN con CHAR(20))
BEGIN
  SELECT Name, HeadOfState FROM Country
  WHERE Continent = con;
END

Test the stored procedure.

 CALL Country('India');

Example 2:

Include input output parameters.

CREATE  PROCEDURE `SpCountry`(IN $Mode tinyint UNSIGNED, INOUT $Code varchar(100),
$BranchCode varchar(100), $Country varchar(50), $ShortDescription varchar(20),
$isActive tinyint, $IsCanceled tinyint, $UserCode varchar(100))
BEGIN
  IF $Mode = 0 THEN
    SELECT
      @Code := NEWCODE($BranchCode);
    SET $Code = @Code;
    INSERT INTO country (Code, BranchCode, country, ShortDescription, isActive, CreatedBy, CreatedOn)
      VALUES ($Code, $BranchCode, $Country, $ShortDescription, $isActive, $UserCode, NOW());
  ELSE
    IF $Mode = 1 THEN
      UPDATE country
      SET country = $Country,
          ShortDescription = $ShortDescription,
          isActive = $isActive,
          LastUpdatedBy = $UserCode,
          LastUpdatedOn = NOW()
      WHERE Code = $Code;
    ELSE
      IF $Mode = 2 THEN
        UPDATE country
        SET IsCanceled = $IsCanceled,
            CanceledOn = NOW(),
            CanceledBy = $UserCode
       WHERE (Code = $Code);
      ELSE
        IF $Mode = 3 THEN
          IF $IsCanceled IS NULL THEN
            SELECT
              Code,
              country,
              ShortDescription,
              isActive,
              IsCanceled
            FROM country
            ORDER BY country;
          ELSE
            SELECT
              Code,
              country,
              ShortDescription,
              isActive,
              IsCanceled
            FROM country
            WHERE IsCanceled = $IsCanceled
            ORDER BY country;
          END IF;
        ELSE
          IF $Mode = 4 THEN
            SELECT
              Code,
              country,
              ShortDescription,
              isActive,
              IsCanceled
            FROM country
            WHERE (Code = $Code);
          END IF;
        END IF;
      END IF;
    END IF;
  END IF;
END ;