As many of you know I am a big fan of the SQL Server Performance Tuning area and I spend most of the time working with the client on various SQL Server Performance Tuning projects. The best part of my job is that I get to work with the new client every day on my favorite topic of Comprehensive Database Performance Health Check. Recently while working with a client on a system walked into a very interesting situation where I had to write a stored procedure in three different programming languages MySQL, MariaDB and SQL Server. While it was easy for me to write a stored procedure in SQL Server, I found that for MariaDB and MySQL one has to know a bit of syntax to write the very first stored procedure.
The goal of this blog post is to provide a template for anyone who wants to write their very first stored procedure with MariaDB or MySQL.
Let us see how we can write the stored procedure in the MariaDB and MySQL. Please note that the stored procedure does not have to accept any parameter and also does not interact with any database table. It just returns value 1 when we execute it.
-- Change the delimiter DELIMITER // -- Create the stored procedure CREATE OR REPLACE PROCEDURE FirstProc() BEGIN SELECT 1 AS OurResult; END // -- Change back the delimiter DELIMITER ; -- Call the stored procedure CALL FirstProc(); -- remove the stored procedure DROP PROCEDURE FirstProc;
Well, that’s it. We have successfully created our stored procedure.
It is worth noting that in MariaDB and MySQL when you create the stored prcedure you have to specify parenthesis () after the name of the stored procedure. However, there is no need for the parenthesis when we are dropping the stored procedure.
If you want to start learning MariaDB or MySQL, here is the link where you can learn more about it:
Let me know if you have any good tricks for SQL Server, I will be happy to post on the blog with due credit to you.
Reference: Pinal Dave (https://blog.sqlauthority.com)