{"id":9,"date":"2012-07-07T04:43:53","date_gmt":"2012-07-07T04:43:53","guid":{"rendered":"http:\/\/codeatelier.wordpress.com\/?p=9"},"modified":"2016-09-10T14:13:40","modified_gmt":"2016-09-10T14:13:40","slug":"mysql-procedure-part-1","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/mysql-procedure-part-1\/","title":{"rendered":"MySQL Procedure Part1"},"content":{"rendered":"<p>A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.<\/p>\n<p>Now let us see how to Create stored procedure in mysql<br \/>\n<strong>Code for Simple Procedure<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDELIMITER \/\/\r\nCREATE PROCEDURE Sample()\r\nBEGIN\r\n  SELECT 'Hello World !';\r\nEND\/\/\r\n<\/pre>\n<p><strong>Calling Procedure<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCALL Sample()\r\n\r\n&lt;strong&gt;Deleting Procedure if Already exists&lt;\/strong&gt;\r\n&#x5B;code lang=&quot;sql&quot;]\r\nDELIMITER \/\/\r\nDROP PROCEDURE\u00a0 IF EXISTS ShowPages;\r\nCREATE PROCEDURE ShowPages()\r\nBEGIN\r\n  SELECT *\r\n    FROM Pages;\r\nEND\r\n<\/pre>\n<p><strong>Supplying a Argument to Procedure<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDELIMITER \/\/\r\nDROP PROCEDURE\u00a0 IF EXISTS ShowPages;\r\nCREATE PROCEDURE ShowPages(IN Var1 VARCHAR(30))\r\nBEGIN\r\n  SELECT *\r\n    FROM Pages\r\n   WHERE PageName = Var1;\r\nEND\r\n<\/pre>\n<p><strong>Working with Supplied Argument<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDELIMITER \/\/\r\nDROP PROCEDURE\u00a0 IF EXISTS ShowPages;\r\nCREATE PROCEDURE ShowPages(IN Var1 INT)\r\nBEGIN\r\n   SELECT Var1 + 10;\r\nEND\r\n<\/pre>\n<p><strong>Calling Procedure<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCALL ShowPages(10)\r\n<\/pre>\n<p><strong>O\/P:<\/strong><br \/>\n20<\/p>\n<p><strong>Getting output from Procedure<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDELIMITER \/\/\r\nDROP PROCEDURE\u00a0 IF EXISTS ShowPages;\r\nCREATE PROCEDURE ShowPages(OUT Var1 VARCHAR(30))\r\nBEGIN\r\n  SET Var1 = 'Mugilvannan';\r\n  SELECT Var1;\r\nEND\/\/\r\n<\/pre>\n<p><strong>Calling Procedure<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCALL ShowPages(@parameter_var1);\r\n<\/pre>\n<p><strong>O\/P:<\/strong><br \/>\nMugilvannan<\/p>\n<p><strong>Returns The Last Inserted ID<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDELIMITER \/\/\r\nDROP PROCEDURE\u00a0 IF EXISTS\u00a0 InsertSubjects;\r\nCREATE PROCEDURE InsertSubjects(OUT Id INT, IN strMenuName VARCHAR(30),IN strPosition INT, IN strVisible TINYINT)\r\nBEGIN\r\n  INSERT INTO Subjects(MenuName, Position, Visible)\r\nVALUES(strMenuName, strPosition, strVisible);\r\n\r\n SELECT LAST_INSERT_ID()\r\n   FROM Subjects\r\n  LIMIT 1;\r\nEND\/\/\r\n<\/pre>\n<p><strong>CALLING ONE PROCEDURE WITHIN ANOTHER PROCEDURE<\/strong><br \/>\n<strong>Procedure1 mp_proc1 Will return value returned by procedure 2<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n\/\/PROCEDURE 1\r\nDROP PROCEDURE IF EXISTS mp_proc1;\r\nCREATE PROCEDURE mp_proc1(OUT AmenityName1 VARCHAR(100))\r\nBEGIN\r\n CALL mp_proc2(@amnName);\r\n SET AmenityName1 = (SELECT @amnName); \r\nEND\r\n\r\n\/\/PROCEDURE 2\r\nDROP PROCEDURE IF EXISTS mp_proc2;\r\nCREATE PROCEDURE mp_proc2(OUT AmenityName VARCHAR(100))\r\nBEGIN\r\n SET AmenityName =(SELECT amenity_name \r\n                     FROM mp_amenities\r\n                    WHERE id = 2);\r\nEND\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure. Now let us see how to Create stored procedure in mysql Code for Simple Procedure DELIMITER \/\/ CREATE PROCEDURE Sample() BEGIN&hellip; <a href=\"https:\/\/codethataint.com\/blog\/mysql-procedure-part-1\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[87],"tags":[],"class_list":["post-9","post","type-post","status-publish","format-standard","hentry","category-procedures"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/9","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=9"}],"version-history":[{"count":4,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/9\/revisions"}],"predecessor-version":[{"id":1581,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/9\/revisions\/1581"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=9"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=9"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=9"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}