{"id":287,"date":"2009-12-05T19:42:01","date_gmt":"2009-12-05T19:42:01","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=287"},"modified":"2024-02-09T04:19:26","modified_gmt":"2024-02-09T04:19:26","slug":"stored-procedure-execute-query-if-table-or-column-exists","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/stored-procedure-execute-query-if-table-or-column-exists","title":{"rendered":"MySQL Stored procedure \u2013 Execute query if table or Column exists"},"content":{"rendered":"Well procedures mainly carried out working with information schema and it&#8217;s usage in stored procedure.\u00a0Procedures are fairly simple and easy to understand.\r\n\r\n<strong>1. Edit_table \u2013 following procedure executes queries to particular table if it exists.<\/strong>\r\n\r\n&nbsp;\r\n\r\nBasically I created it to satisfy a need of altering a table if column exists.\r\n\r\nNow it can be used to execute any query if table exists.\r\n\r\n\r\n<pre>Usage: call Edit_table(database-name,table-name,query-string);<\/pre>\r\n&#8211; Procedure will check for existence of table-name under database-name and will execute query-string if it exists.\r\n\r\n<strong>Download Stored Procedure: <a title=\"\" href=\"https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2009\/12\/edit_table.sql\">Edit_table<\/a><\/strong>\r\n\r\n\r\n<pre class=\"wp-block-code\"><code>DELIMITER $$\r\n\r\nDROP PROCEDURE IF EXISTS `Edit_table` $$\r\nCREATE PROCEDURE `Edit_table` (in_db_nm varchar(20),in_tbl_nm varchar(20),in_your_query varchar(200))\r\nDETERMINISTIC\r\nBEGIN\r\n\r\nDECLARE var_table_count INT;\r\n\r\nselect count(*) INTO @var_table_count from information_schema.TABLES where\u00a0 TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm;\r\nIF (@var_table_count &gt; 0) THEN\r\nSET @in_your_query = in_your_query;\r\n#SELECT @in_your_query;\r\nPREPARE my_query FROM @in_your_query;\r\nEXECUTE my_query;\r\n\r\nELSE\r\nselect \"Table Not Found\";\r\nEND IF;\r\n\r\nEND $$\r\nDELIMITER ;<\/code><\/pre>\r\n&nbsp;\r\n\r\n<strong>1A. A slight variation of this procedure is editing a table if column exists.<\/strong>\r\n\r\n&nbsp;\r\n\r\n<strong>Download Stored Procedure: <\/strong><a title=\"\" href=\"https:\/\/kedar.nitty-witty.com\/blog\/wp-content\/uploads\/2009\/12\/edit_table_column.sql\" target=\"_blank\" rel=\"noopener\"><strong>Edit_table_column<\/strong><\/a>\r\n\r\n&nbsp;\r\n\r\n\r\n<pre class=\"wp-block-code\"><code>DELIMITER $$\r\n\r\nDROP PROCEDURE IF EXISTS `Edit_table_column` $$\r\nCREATE 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))\r\nDETERMINISTIC\r\nBEGIN\r\n\r\nDECLARE var_table_count INT;\r\n\r\nselect count(*) INTO @var_table_count from information_schema.COLUMNS where\u00a0 TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm and COLUMN_NAME=in_colm_nm;\r\nIF (@var_table_count &gt; 0) THEN\r\nSET @in_your_query = in_your_query;\r\nSELECT @in_your_query;\r\nPREPARE my_query FROM @in_your_query;\r\nEXECUTE my_query;\r\n\r\nELSE\r\nselect \"Table Not Found\";\r\nEND IF;\r\n\r\nEND $$\r\nDELIMITER ;<\/code><\/pre>\r\n&nbsp;\r\n\r\n\r\n<pre class=\"wp-block-code\"><code>Usage: call Edit_table_column(database-name,table-name,column-name,query-string);<\/code><\/pre>\r\n","protected":false},"excerpt":{"rendered":"Well procedures mainly carried out working with information schema and it&#8217;s usage in stored procedure.\u00a0Procedures are fairly simple and easy to understand. 1. Edit_table \u2013 following procedure executes queries to&hellip;\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,378],"tags":[918,210,211,919,108],"class_list":{"0":"post-287","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-scripts-mysql","8":"tag-alter-table-if-column-exist","9":"tag-create-table-if-exists","10":"tag-execute-query-if-exists","11":"tag-mysql-conditional-command-execution","12":"tag-stored-procedure"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=287"}],"version-history":[{"count":10,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/287\/revisions"}],"predecessor-version":[{"id":3264,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/287\/revisions\/3264"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}