Stored Procedures en MySQL 5.x

Buscando algunos SP's para un problema que tenia que resolver, llegue a esta pagina quye trae varios que son muy muy utiles :p

Aqui se los dejo:

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 ;