Project mySQL SP: Guide to creating and calling a stored procedure in mySQL.

Purpose
The purpose of this tutorial is to show how to create a stored procedure in mySQL, how to call that stored procedure as well as calling stored procedures from other stored procedures and passing data between them. At the bottom of the page I've also included some other handy tips for performing a few administrative functions in mySQL Workbench that you don't need to perform in a terminal window (which can be handy if you don't have terminal "command-line" access). It is recommended that you use mySQL Workbench, developed by Oracle, for database work (if you don't already). It is somewhat similiar to Microsoft's SQL UI.

The main purpose of a stored procedure is to have a single block of code in a single location that you can reference by the name of the stored procedure in many different places. This helps minimize the amount of code updating that may be required if you alter a table and things of that nature; it also helps improve security. You also gain the ability to create temporary tables and use other mySQL features that you cannot normally use with mySQL statements. While stored procedures are a powerful asset to leverage, I'm surprised that they are not more often used.

Before getting into this tutorial (below the video) you may want to check out the video tutorial put together by Webucator on the subject of stored procedures:

Now, let's begin by creating a stored procedure in mySQL:

DELIMITER $$
-- We'll name the stored procedure "retrieveID"
CREATE DEFINER=`your-database-name`@`localhost` PROCEDURE `retrieveID`
-- We'll pass one parameter to the stored procedure
(
IN flag INT
)
COMMENT 'Always have a well defined comment, or description, regarding the purpose / scope of the stored procedure'
-- This stored procedure will only read data from the database but if it were to write data then it would MODIFIES SQL DATA
READS SQL DATA
BEGIN
SELECT
	i.id
FROM inventory i
WHERE i.flag = flag
;
END$$

Once that block of code (above) is highlighted and executed the stored procedure should be created. While it is up to your development standards as to how to name the stored procedure, there usually is no need to add "sp" to the name of the procedure since stored procedures are stored in a single location in the database and the method with which to call them is unique. It is important to note, however, that if you ever need to change a stored procedure in mySQL you have to delete it (DROP) from the database and recreate it. With Microsoft SQL, on the other hand, you can simply edit the stored procedure (there is no requirement to recreate it).

So, how do you call the stored procedure that we just created? It is fairly straight-forward as shown below:

call retrieveID(1);

The next common task that may be encountered with a stored procedure is the use of a cursor. In mySQL, a cursor is another name for a method to loop through data and perform actions on that data being looped through. Let's look at a stored procedure that uses a cursor:

DELIMITER $$
CREATE DEFINER=`your-database-name`@`localhost` PROCEDURE `retrieveInventoryNames`
(
flag INT
,iterationLimitMaxReturnedResults INT
)
COMMENT 'Always have a well defined comment, or description, regarding the purpose / scope of the stored procedure.'
MODIFIES SQL DATA
BEGIN

-- Flag indicating looping should end when set to 1
DECLARE terminate INT DEFAULT 0;

-- Define the variable that will hold the inventory id of the item
-- that we will get the names from
DECLARE currentitemid INT;

-- Use cursor to iterate through the table inventory to get the names
DECLARE itemids CURSOR FOR
	SELECT
		i.itemid
	FROM inventory i
	WHERE i.flag = 1
	ORDER BY i.itemid ASC
	LIMIT 0, iterationLimitMaxReturnedResults;

-- Define the value of terminate when there are no more rows to iterate through
DECLARE CONTINUE HANDLER FOR NOT FOUND SET terminate = 1;

-- Define temporary table that will hold the results
-- If you don't drop the table and have already run the stored procedure okay, the table will still be in the database
DROP TEMPORARY TABLE IF EXISTS collection;
CREATE TEMPORARY TABLE collection(
	itemid INT
	,fullname VARCHAR(255)
);

-- Initialize the cursor
OPEN itemids;

-- Loop through the cursor
repeat_loop: LOOP
	FETCH itemids INTO currentitemid;
	IF terminate = 1 THEN
		LEAVE repeat_loop;
	END IF;

	-- Insert the select results into the temporary table
	INSERT INTO collection (
		SELECT
			i.itemid
			,TRIM(i.fullname) AS 'fullname'
		FROM inventory i
		WHERE i.itemid = currentitemid
	);
END LOOP repeat_loop;

-- Release the cursor
CLOSE itemids;

-- Return content of the temporary table
SELECT * FROM collection c ORDER BY c.fullname ASC;

-- Release the temporary table
DROP TEMPORARY TABLE IF EXISTS collection;

END$$

As you can see from the example, the use of a CURSOR is not too involved. But, how do you call a stored procedure that uses a CURSOR / temporary table? The same as any other stored procedure:

call retrieveInventoryNames(1, 1000);

Calling a stored procedure from within a different stored procedure is straight-forward. However, if you need to call a stored procedure (child) from within a different stored procedure (parent) and you need to pass back values to the parent from the child may be accomplished by:

DELIMITER $$
CREATE DEFINER=`your-database-name`@`localhost` PROCEDURE `childroutine` (
IN inputstring VARCHAR(32)
, INOUT outputstring VARCHAR(64)
)
COMMENT 'This child routine will add some text it was passed from the parent routine and then return that modified text to the parent routine.'
BEGIN
	DECLARE sModString VARCHAR(64) DEFAULT '';
	SET sModString = CONCAT(inputstring, ' -added text from child routine');
	SELECT sModString INTO outputstring;
END$$

DELIMITER $$
CREATE DEFINER=`your-database-name`@`localhost` PROCEDURE `parentroutine` ()
COMMENT 'This parent routine will pass some text to the childroutine for additional operations and then output what the child routine changed.'
BEGIN
	DECLARE sModString VARCHAR(32) DEFAULT '';
	DECLARE sChildResult VARCHAR(64) DEFAULT '';
	SET sModString = 'parent text';
	-- Call the child routine, passing in one value
	CALL childroutine(sModString, @outputstring);
	-- Get the output of the child routine
	SELECT @outputstring INTO sChildResult;
	-- Show the output from the child routine
	SELECT sChildResult;
END$$

Other Handy Tips
If you are working with mySQL from Workbench, but may not have access to perform a variety of traditional (or "old school") commands via a terminal window, you may like to know that an assortment of administrative actions can be completed from within mySQL Workbench. Let's look at a few along with other handy statements:

Get a list of currently executing threads (such as select statements currently running):

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST isp WHERE isp.Command = 'Query';

Column "INFO" will contain the mysql query text and column "ID" is the connection id. In place of this select statement you could use "SHOW PROCESSLIST" but you cannot limit returned data by a specific value.

Stop the mysql query that is desired by specifying the connection id:

KILL QUERY 207377; -- 207377 is an example of whatever the actual connection id is

Find out if a key (index) exists on a column in a table in the database:

SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'name-of-table' AND INDEX_NAME = 'column-name';

Update the column so that it has a key (index):

ALTER TABLE `your-database-name`.`name-of-table` ADD INDEX `column-name` (`column-name` ASC); -- Depending on what is needed DESC may be preferred over ASC

Get table rows when no primary key was defined for a table (useful with select statements):

SELECT *, char_length('') FROM name-of-table ORDER BY column-name ASC;

Locate all routines for a database:

SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE';

Identify tables in a database which have foreign key constraints:

SELECT isrc.TABLE_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS isrc WHERE isrc.CONSTRAINT_SCHEMA = 'your-database-name';

Get a list of tables in a database:

SELECT ist.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ist WHERE ist.TABLE_SCHEMA = 'your-database-name' AND ist.TABLE_TYPE = 'BASE TABLE';

Show the current mySQL Server version you are connected to:

SELECT @@VERSION;

Get the disk consumption size of all tables in a database:

SELECT
	SUM(ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2))  as "Size in GB"
FROM information_schema.TABLES
WHERE table_schema = "your-database";

Miscellaneous functions:

USE `your-database-name`;
DROP VIEW IF EXISTS `name-of-view`;
DROP TRIGGER IF EXISTS `name-of-trigger`;
DROP TABLE IF EXISTS `name-of-table`;
DROP FUNCTION `name-of-function`;
RENAME TABLE `current-table-name` TO `new-table-name`;
ALTER TABLE `current-table` MODIFY COLUMN `column-name` VARCHAR(50);