05 Dec

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);

2 thoughts on “MySQL Stored procedure – Execute query if table or Column exists

  1. i have a table and table have a column data ,in data column i write a query like “select * from table name” then how i will execute both table data and columnn query data also

    • Hi Gaurav,

      sorry I couldn’t get your question! It’d be better if you can put some sample data/table and desired result!

      Thanks,
      Kedar.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

-- Kedar Vaijanapurkar --