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

June 26, 2009

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.

Download the Help database.

Now we’re ready to import the database tables.

From the Import menu, click the “Load SQL File…” item and select the Help.sql file in the “Open” dialog:

That will open the file in a new Query editor window. Click the blue arrow button in the toolbar to execute the query (not the Query tab icon!). It’ll take a minute or two for the database to be added and the tables populated with data.

Once the app finishes, you may have to refresh the database tree pane on the left-hand side by right-clicking it and selecting “Refresh” from the popup menu. Alternatively you can just hit the F5 key.

If you expand the help database, you’ll see that it contains four tables. It is these that we will be reading from in our stored procedure:

Our procedure will perform a search of the help topics using several input parameters.

The help_search Stored Procedure

A stored procedure, or proc for short, is one of two types of stored routines. The other is a stored function. In the database world, a stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead. A user defined function (UDF) and a stored procedure share some similarities but there exists a difference between their implementation in the code. A stored procedure needs to be invoked whereas a UDF can be used like any other statement.

You can launch the “Stored routine editor” in HeidiSQL by clicking the “Create stored routine” button on the toolbar:

We’ll Name our PROC “search_help”. Accept the default Type of “Procedure (doesn’t return a result)”. This disables the Returns text field because PROCs use a different mechanism than functions for returning values. In MySQL, the Data Access options are advisory only. The server does not use them to constrain what kinds of statements a routine will be allowed to execute. Here’s an explanation of each option:

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SET @x = 1 or DO RELEASE_LOCK('abc'), which execute but neither read nor write data.
• NO SQL indicates that the routine contains no SQL statements.
• READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT), but not statements that write data.
• MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

The SQL SECURITY property can be used to specify whether the routine should be executed using the permissions of the user who creates the routine or the user who invokes it. The default value is DEFINER. The creator or invoker must have permission to access the database with which the routine is associated.

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