MySQL - how to create a Stored Procedure

Creating StoredProcs still feels a little like magic at the moment, but it’s incredibly simple really.

Stored Procs (aka “SPs” or “SProcs”) live within the database and allow a number of activities to be chained together. They also represent an abstraction method so you can help de-couple DB and application code.

However, MySQL didn’t have Stored Procs until a year or two ago and because of this they’re not widespread.

The first thing to remember with MySQL Stored Procs is that the mysql engine will get confused if you write the SP and try and apply it using MySQL Query Browser or just using the mysql command line app; it’ll notice semicolons within your SP and try and apply those and it won’t work. Thus you need to change the delimiter before you define the Stored Proc. We do this by making it // instead of ; (a semicolon) using the “delimiter” command, we then swap it back to using semicolons at the end.
Here’s a standard bit of text that you can re-use as a template.

delimiter //
DROP PROCEDURE IF EXISTS xxx//
CREATE PROCEDURE xxx (IN param1 VARCHAR(30), OUT param2 VARCHAR(40))
BEGIN
DECLARE localvar VARCHAR(5);
SET localvar = 'helo';
SET param2 = (SELECT MD5(CONCAT(localvar, param1)) );
/* return the data here using a select statement (incase it's not easy for the caller to use the out param) */
SELECT param2 as md5hashedvarchar;
END;
//
delimiter ;

91 Responses to “MySQL - how to create a Stored Procedure”

  1. John407 Says:

    Very nice site!
    cheap viagra

  2. John407 Says:

    Very nice site!
    [url=http://training.cvc4.org/pharm1/14225/2.html]cheap cialis[/url]

  3. John407 Says:

    Very nice site!
    [LINK http://training.cvc4.org/pharm1/14225/3.html]cheap tramadol[/LINK]

  4. John407 Says:

    Very nice site!
    http://training.cvc4.org/pharm1/14225/4.html

  5. John407 Says:

    Very nice site!

  6. John363 Says:

    Very nice site!
    cheap viagra

  7. John363 Says:

    Very nice site!
    [url=http://training.cvc4.org/pharm1/14227/2.html]cheap cialis[/url]

  8. John363 Says:

    Very nice site!
    [LINK http://training.cvc4.org/pharm1/14227/3.html]cheap tramadol[/LINK]

  9. John363 Says:

    Very nice site!
    http://training.cvc4.org/pharm1/14227/4.html

  10. John363 Says:

    Very nice site!

  11. John1077 Says:

    Beautiful site!
    cheap viagra

  12. John1077 Says:

    Great work,webmaster,nice design!
    viagra

  13. John1077 Says:

    If you have to do it, you might as well do it right
    buy viagra

  14. John1077 Says:

    Excellent site. It was pleasant to me.
    buy viagra

  15. John1077 Says:

    I want to say - thank you for this!
    cheap viagra

  16. John1077 Says:

    Perfect work!
    viagra

  17. John1077 Says:

    Nice site! Thank you!
    viagra

  18. John1077 Says:

    Excellent site. It was pleasant to me.
    viagra

  19. John1077 Says:

    Great work,webmaster,nice design!
    cheap viagra

  20. John1077 Says:

    Perfect site, i like it!
    cheap viagra

  21. John1077 Says:

    It is the coolest site,keep so!
    buy viagra

  22. John1077 Says:

    I want to say - thank you for this!
    viagra

  23. John1077 Says:

    It is the coolest site,keep so!
    viagra

  24. John1077 Says:

    Great site. Keep doing.
    viagra

  25. John1077 Says:

    Beautiful site!
    viagra

  26. John1077 Says:

    Beautiful site!
    cheap viagra

  27. John1077 Says:

    Great site. Keep doing.
    buy viagra

  28. John1077 Says:

    Perfect site, i like it!
    buy viagra

  29. John1077 Says:

    Perfect work!
    buy viagra

  30. John1077 Says:

    I bookmarked this guestbook. Thank you for good job!
    viagra

  31. John1077 Says:

    If you have to do it, you might as well do it right
    cheap viagra

  32. John1077 Says:

    Incredible site!
    viagra

  33. John1077 Says:

    If you have to do it, you might as well do it right
    viagra

  34. John1077 Says:

    It is the coolest site,keep so!
    viagra

  35. John1077 Says:

    If you have to do it, you might as well do it right
    cheap viagra

  36. John1077 Says:

    Beautiful site!
    buy viagra

  37. John1077 Says:

    Beautiful site!
    buy viagra

  38. John1077 Says:

    Incredible site!
    buy viagra

  39. John1077 Says:

    Beautiful site!
    viagra

  40. John1077 Says:

    If you have to do it, you might as well do it right
    buy viagra

  41. John1077 Says:

    Great .Now i can say thank you!
    buy viagra

  42. John1077 Says:

    It is the coolest site,keep so!
    cheap viagra

  43. John1077 Says:

    Great site. Keep doing.
    buy viagra

  44. John1077 Says:

    Perfect work!
    cheap viagra

  45. John1077 Says:

    Great site. Keep doing.
    viagra

  46. John1077 Says:

    Great site. Good info
    cheap viagra

  47. John1077 Says:

    Incredible site!
    viagra

  48. John1077 Says:

    Great site. Keep doing.
    cheap viagra

  49. John1077 Says:

    I want to say - thank you for this!
    buy viagra

  50. John1077 Says:

    Great site. Good info
    cheap viagra

  51. John1077 Says:

    Great .Now i can say thank you!
    viagra

  52. John1077 Says:

    Great .Now i can say thank you!
    cheap viagra

  53. John1077 Says:

    Excellent site. It was pleasant to me.
    cheap viagra

  54. John1077 Says:

    Perfect site, i like it!
    cheap viagra

  55. John1077 Says:

    Very interesting site. Hope it will always be alive!
    cheap viagra

  56. John1077 Says:

    Great site. Good info
    buy viagra

  57. John1077 Says:

    I want to say - thank you for this!
    viagra

  58. John1077 Says:

    Incredible site!
    cheap viagra

  59. John1077 Says:

    Very interesting site. Hope it will always be alive!
    viagra

  60. John1268 Says:

    Great .Now i can say thank you!
    viagra

  61. John1268 Says:

    Nice site! Thank you!
    viagra

  62. John1268 Says:

    Perfect work!
    viagra

  63. John1268 Says:

    Great work,webmaster,nice design!
    buy viagra

  64. John1268 Says:

    Perfect site, i like it!
    buy viagra

  65. John1268 Says:

    It is the coolest site,keep so!
    cheap viagra

  66. John1268 Says:

    Perfect site, i like it!
    viagra

  67. John1268 Says:

    Great site. Good info
    viagra

  68. John1268 Says:

    Very interesting site. Hope it will always be alive!
    viagra

  69. John1268 Says:

    Perfect site, i like it!
    viagra

  70. John1268 Says:

    Very interesting site. Hope it will always be alive!
    buy viagra

  71. John1268 Says:

    Excellent site. It was pleasant to me.
    viagra

  72. John1268 Says:

    I bookmarked this guestbook. Thank you for good job!
    cheap viagra

  73. John1268 Says:

    Nice site! Thank you!
    cheap viagra

  74. John1268 Says:

    Incredible site!
    cheap viagra

  75. John1268 Says:

    Great .Now i can say thank you!
    cheap viagra

  76. John1268 Says:

    I want to say - thank you for this!
    cheap viagra

  77. John1431 Says:

    I bookmarked this guestbook. Thank you for good job!
    buy viagra

  78. John1431 Says:

    Great site. Keep doing.
    cheap viagra

  79. John1431 Says:

    If you have to do it, you might as well do it right
    viagra

  80. John1431 Says:

    Excellent site. It was pleasant to me.
    cheap viagra

  81. John1431 Says:

    Perfect work!
    buy viagra

  82. John1431 Says:

    Great work,webmaster,nice design!
    viagra

  83. John1431 Says:

    I bookmarked this guestbook. Thank you for good job!
    buy viagra

  84. John1431 Says:

    It is the coolest site,keep so!
    buy viagra

  85. John1431 Says:

    Very interesting site. Hope it will always be alive!
    buy viagra

  86. John1237 Says:

    I want to say - thank you for this!
    buy viagra

  87. John1510 Says:

    I bookmarked this guestbook. Thank you for good job!
    viagra

  88. John957 Says:

    Great .Now i can say thank you!
    buy viagra

  89. John1156 Says:

    Very nice site!
    cheap viagra

  90. John1156 Says:

    Very nice site!
    [url=http://training.cvc4.org/pharm1/14250/2.html]cheap cialis[/url]

  91. John1156 Says:

    Very nice site!
    [LINK http://training.cvc4.org/pharm1/14250/3.html]cheap tramadol[/LINK]

Leave a Reply