Creating Stored Procedures in MySQL Using HeidiSQL 4’s Stored Routine Editor

In the ”Write MySQL Queries Using HeidiSQL 4” article, we learned how to connect to a MySQL database and execute queries against it using the free HeidiSQL GUI client. The latest version adds many enhancements and new features, one of which is an editor for creating stored procedures and functions. Today, we’ll learn how to create a stored procedure using HeidiSQL’s Stored Procedure Editor.

Create the Help Database

We’ll be using the same database as we did last time, named Help.

If you haven’t already done so, visit the HeidiSQL website, download version 4 and install it, following their instructions.

Launch HeidiSQL to display the Session Manager:

For our purposes you can use the default MySQL session and user account, just to keep things simple. Hit the Connect button to open the main application window.

A procedure or function is considered deterministic if it always produces the same result for the same input parameters, and not deterministic otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC.

Our proc will have two input parameters called “topic” and “keyword”. Using combinations of the two will allow for searching by either or both parameters. To add parameters to the proc, click the Add button on the left of the Parameters list. Values can be typed directly in the row. Both parameters will be of type VARCHAR, which is short for “Variable Character” field. In other words, the parameters may contain any number of characters up to the length that we assign to it. There are no hard and fast rules regarding length, except that one’s aim should be to allocate enough characters to accomodate the values, but without going overboard as to waste memory. I decided that a maximum length of 100 characters should be more than adequate. Be sure to enter a space after the closing parameter or it will cause a syntax error in the generated SQL. The default Context of “IN” is what we want. In addition to IN, parameters can also be of type OUT and INOUT:

• IN parameters are those which are passed to the proc
• OUT parameters are those which are returned from it
• INOUT act like variables which are passed by reference so that the input parameter can be modified by the proc and then returned to the caller. If you know that the execution of your SQL statement will always return a single row of data, then you should consider using output parameters in place of a single-row select statement.

Here is what the Stored routine dialog should look like at this point:

Now let’s move on to the Routine body.

You can maximize the window before writing the body in order to increase the size of the textarea. However, an even better approach is to write and test the query in the SQL editor, as we learned in the last article, and paste the code in the Routine Body textarea once all the kinks have been worked out. As you may recall, besides offering plenty of room, the Query tab also features an output console below it and supports auto-completion:

To bring up the results shown in the image above, enter the following code in the Query tab:

	SELECT 	hc.name AS category,
		ht.name AS topic,
		ht.description,
		ht.example
	
	FROM	help_category	AS hc,
		help_topic	AS ht
	
	WHERE	hc.help_category_id 	= ht.help_category_id
	AND	ht.name 		= 'CREATE USER'

…and click the Execute SQL button to try out the query:

That code represents what the proc will execute when the keyword parameter is supplied by itself.

The second possibility is that only the keyword is supplied. Here is the code for that case:

	SELECT 	hc.name AS category,
		ht.name AS topic,
		ht.description,
		ht.example
					     
	FROM	help_category	AS hc,
		help_topic	AS ht,
		help_keyword	AS hk,
		help_relation	AS hr
					
	WHERE	hc.help_category_id 	= ht.help_category_id
	AND	ht.help_topic_id	= hr.help_topic_id
	AND	hk.help_keyword_id	= hr.help_keyword_id
	AND	hk.name 		= 'BY';

The third possibility is that both parameters are supplied. In that case, we’ll retrieve only the rows that satisfy both conditions:

	SELECT 	hc.name AS category,
		ht.name AS topic,
		ht.description,
		ht.example
					     
	FROM	help_category	AS hc,
		help_topic	AS ht,								
		help_keyword	AS hk,
		help_relation	AS hr
					
	WHERE	hc.help_category_id	= ht.help_category_id
	AND	ht.help_topic_id	= hr.help_topic_id		
	AND	hk.help_keyword_id	= hr.help_keyword_id
	AND	ht.name		= 'CREATE USER'
	AND	hk.name		= 'BY';

In a situation where neither parameter has been supplied, nothing will be returned.

Putting it All Together

We’ll combine the three SQL statements above using an IF/ELSE statement. We can test for an empty parameter by following it with “IS NULL”. Therefore, a topic search will be performed if the keyword parameter IS NULL, a keyword search will be performed if the topic parameter is empty, and a combined search will be performed otherwise.

BEGIN
	IF keyword IS NULL THEN
		SELECT 	hc.name 	AS category,
			ht.name 	AS topic,
			ht.description,
			ht.example
					     
		FROM	help_category	AS hc,
			help_topic	AS ht
					
		WHERE	hc.help_category_id 	= ht.help_category_id
		AND	ht.name			= topic;
	ELSEIF topic IS NULL THEN
		SELECT 	hc.name AS category,
			ht.name AS topic,
			ht.description,
			ht.example
					     
		FROM	help_category	AS hc,
			help_topic	AS ht,
			help_keyword	AS hk,
			help_relation	AS hr
					
		WHERE	hc.help_category_id 	= ht.help_category_id
		AND	ht.help_topic_id	= hr.help_topic_id
		AND	hk.help_keyword_id	= hr.help_keyword_id
		AND	hk.name 		= keyword;
	ELSE
		SELECT 	hc.name AS category,
			ht.name AS topic,
			ht.description,
			ht.example
					     
		FROM	help_category	AS hc,
			help_topic	AS ht,
			help_keyword	AS hk,
			help_relation	AS hr
					
		WHERE	hc.help_category_id 	= ht.help_category_id
		AND	ht.help_topic_id	= hr.help_topic_id
		AND	hk.help_keyword_id	= hr.help_keyword_id
		AND	ht.name 		= topic
		AND	hk.name 		= keyword;
	END IF;
END

Calling your new Stored Procedure

As mentioned previously, a stored procedure needs to be invoked in order to run. To do that, go to the Query tab and enter the following:

CALL search_help('HEX', 'UNLOCK');

CALL search_help('CREATE USER, 'BY'); will also work.

Then execute the statement by clicking the “Execute Line” button:

As with regular queries, the returned columns will be displayed in the results pane directly beneath the SQL window:

HeidiSQL 4’s Stored Routine Editor offers a user-friendly alternative to using a command-line interface to create and manage your stored procedures. In the next article, we’ll cover how to make use of MySQL functions, as well as how to use HeidiSQL 4’s Stored Routine Editor to create your own custom functions.

» See All Articles by Columnist Rob Gravelle