MySQL Stored procedure – Execute query if table or Column exists

MySQL Stored procedure – Execute query if table or Column exists

Well procedures mainly carried out working with information schema and it’s usage in stored procedure. Procedures are fairly simple and easy to understand.

1. Edit_table – following procedure executes queries to particular table if it exists.

Basically I created it to satisfy a need of altering a table if column exists.

Now it can be used to execute any query if table exists.

Usage: call Edit_table(database-name,table-name,query-string);

– Procedure will check for existence of table-name under database-name and will execute query-string if it exists.

Download Stored Procedure: Edit_table

DELIMITER $$

DROP PROCEDURE IF EXISTS `Edit_table` $$
CREATE PROCEDURE `Edit_table` (in_db_nm varchar(20),in_tbl_nm varchar(20),in_your_query varchar(200))
DETERMINISTIC
BEGIN

DECLARE var_table_count INT;

select count(*) INTO @var_table_count from information_schema.TABLES where  TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm;
IF (@var_table_count > 0) THEN
SET @in_your_query = in_your_query;
#SELECT @in_your_query;
PREPARE my_query FROM @in_your_query;
EXECUTE my_query;

ELSE
select “Table Not Found”;
END IF;

END $$
DELIMITER ;

1A. A slight variation of this procedure is editing a table if column exists.

Download Stored Procedure: Edit_table_column

DELIMITER $$

DROP PROCEDURE IF EXISTS `Edit_table_column` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Edit_table_column`(in_db_nm varchar(20),in_tbl_nm varchar(20),in_colm_nm varchar(20),in_your_query varchar(200))
DETERMINISTIC
BEGIN

DECLARE var_table_count INT;

select count(*) INTO @var_table_count from information_schema.COLUMNS where  TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm and COLUMN_NAME=in_colm_nm;
IF (@var_table_count > 0) THEN
SET @in_your_query = in_your_query;
SELECT @in_your_query;
PREPARE my_query FROM @in_your_query;
EXECUTE my_query;

ELSE
select “Table Not Found”;
END IF;

END $$
DELIMITER ;

Usage: call Edit_table_column(database-name,table-name,column-name,query-string);