Stored Procedures in MySQL 5.x

As you know MySQL 5.0 introduced Stored Procedures which allow us to automate or program our way out of many tasks on directly on the server rather than having to write external scripts to do complex manipulation of data.

• Stored Procedures in MySQL 5.0
• MySQL 5.0 Reference Manual :: 18 Stored Programs and Views :: 18.2 Using Stored Routines (Procedures and Functions)
• MySQL Stored Procedure Programming by Guy Harrison, Steven Feuerstein

You can find some useful procedures and functions below.
The most important are:

• execute dynamic SQL statement
• raise error/exception

DELIMITER $$
  
  
-- PROCEDURE pExecuteImmediate
--   executes dynamic SQL statement
-- Params:
--   tSQLStmt - SQL statement to be executed
DROP PROCEDURE IF EXISTS `pExecuteImmediate` $$
CREATE PROCEDURE `pExecuteImmediate`(IN tSQLStmt TEXT)
BEGIN
  SET @executeImmediateSQL = tSQLStmt;
  PREPARE executeImmediateSTML FROM @executeImmediateSQL;
  EXECUTE executeImmediateSTML;
  DEALLOCATE PREPARE executeImmediateSTML;
END $$
  
  
-- FUNCTION fFormat
--   replaces '%s' in a string with some value
-- Params:
--   sFormat - string to be formatted
--   sPar1 - value used in replacement
-- Returns:
--   formatted string
DROP FUNCTION IF EXISTS `fFormat` $$
CREATE FUNCTION  fFormat(sFormat TEXT, sPar1 TEXT)
  RETURNS TEXT
BEGIN
  RETURN REPLACE(sFormat, '%s', sPar1);
END $$
  
  
-- PROCEDURE pRaiseError
--   raises error
-- Params:
--   sError - error message
DROP PROCEDURE IF EXISTS `pRaiseError` $$
CREATE PROCEDURE `pRaiseError`(sError VARCHAR(255))
BEGIN
  -- trick
  --   calling of not existing procedure with name that equals error message
  --   will force MySQL exception that looks like error message
  CALL pExecuteImmediate(fFormat('CALL `error: %s, solution`', sError));
END $$
  
  
-- FUNCTION fIsCurrentUserRoot
--   checks if we are connected as root user
-- Returns:
--   0 - table doesn't exist
--   1 - table exists
DROP FUNCTION IF EXISTS `fIsCurrentUserRoot` $$
CREATE FUNCTION `fIsCurrentUserRoot`()
  RETURNS INT
BEGIN
  DECLARE strUser VARCHAR(50);
  DECLARE iRes INT;
  SELECT USER() INTO strUser;
  IF (strUser LIKE 'root%') THEN
    SET iRes = 1;
  ELSE
    SET iRes = 0;
  END IF;
  RETURN iRes;
END $$
  
  
-- FUNCTION fIsTableExists
--   checks if table exists
-- Params:
--   vcTableName - table name
-- Returns:
--   0 - table doesn't exist
--   1 - table exists
DROP FUNCTION IF EXISTS `fIsTableExists` $$
CREATE FUNCTION `fIsTableExists`(vcTableName VARCHAR(50))
  RETURNS INT
BEGIN
  DECLARE iCount INT;
  SELECT count(*) INTO iCount FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE vcTableName AND table_type = 'BASE TABLE';
  RETURN iCount;
END $$
  
  
-- FUNCTION fIsViewExists
--   checks if view exists
-- Params:
--   vcViewName - view name
-- Returns:
--   0 - view doesn't exist
--   1 - view exists
DROP FUNCTION IF EXISTS `fIsViewExists` $$
CREATE FUNCTION `fIsViewExists`(vcViewName VARCHAR(50))
  RETURNS INT
BEGIN
  DECLARE iCount INT;
  SELECT count(*) INTO iCount FROM INFORMATION_SCHEMA.VIEWS WHERE table_name LIKE vcViewName;
  RETURN iCount;
END $$
  
  
-- FUNCTION fIsIndexExists
--   checks if index exists
-- Params:
--   vcTableName - table name
--   vcIndexName - index name
-- Returns:
--   0 - index doesn't exist
--   1 - index exists
DROP FUNCTION IF EXISTS `fIsIndexExists` $$
CREATE FUNCTION `fIsIndexExists`(vcTableName VARCHAR(50), vcIndexName VARCHAR(50))
  RETURNS INT
BEGIN
  DECLARE iCount INT;
  SELECT count(*) INTO iCount FROM INFORMATION_SCHEMA.STATISTICS
    WHERE table_name LIKE vcTableName AND index_name LIKE vcIndexName;
  RETURN iCount;
END $$
  
  
-- FUNCTION fIsColumnExists
--   checks if column exists
-- Params:
--   vcTableName - table name
--   vcColumnName - column name
-- Returns:
--   0 - column doesn't exist
--   1 - column exists
DROP FUNCTION IF EXISTS `fIsColumnExists` $$
CREATE FUNCTION `fIsColumnExists`(vcTableName VARCHAR(50), vcColumnName VARCHAR(50))
  RETURNS INT
BEGIN
  DECLARE iCount INT;
  SELECT count(*) INTO iCount FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name LIKE vcTableName AND column_name LIKE vcColumnName;
  RETURN iCount;
END $$
  
  
DELIMITER ;

Some examples how these procedures and functions can be used...

..from other procedures/functions:

DELIMITER $$
  
  
-- PROCEDURE pDropTable
--   drops table only if it exists
-- Params:
--   vcTableName - table name
DROP PROCEDURE IF EXISTS `pDropTable` $$
CREATE PROCEDURE `pDropTable`(IN vcTableName VARCHAR(50))
BEGIN
  IF (fIsTableExists(vcTableName)=1) THEN
    CALL pExecuteImmediate(CONCAT('DROP TABLE ', vcTableName));
  END IF;
END $$
  
  
-- PROCEDURE pRenameTable
--   renames table only if it exists
-- Params:
--   vcTableName - old table name
--   vcNewTableName - new table name
DROP PROCEDURE IF EXISTS `pRenameTable` $$
CREATE PROCEDURE `pRenameTable`(IN vcTableName VARCHAR(50), IN vcNewTableName VARCHAR(50))
BEGIN
  IF (fIsTableExists(vcTableName)=1) THEN
    CALL pExecuteImmediate(CONCAT('ALTER TABLE ', vcTableName, ' RENAME TO ', vcNewTableName));
  END IF;
END $$
  
  
-- PROCEDURE pCheckCurrentUserRoot
--   raise exception if current user is root
DROP PROCEDURE IF EXISTS `pCheckCurrentUserRoot` $$
CREATE PROCEDURE `pCheckCurrentUserRoot`()
BEGIN
  IF (fIsCurrentUserRoot()=1) THEN
    CALL pRaiseError('Root user is not allowed to run this script');
  END IF;
END $$
  
  
DELIMITER ;

...or from SQL:

SELECT fIsTableExists('test');

SELECT fIsCurrentUserRoot();

CALL pExecuteImmediate('SELECT 1');

CALL pCheckCurrentUserRoot();