Enhance your Queries with Stored Functions

HeidiSQL 4’s Stored
Routine Editor offers a user-friendly alternative to using a command-line
interface to create and manage your stored procedures and functions. In the
last article, we covered how to write a stored procedure using the Stored
Routine Editor. Today, we’ll be learning how to take advantage of some useful
native MySQL functions as well as use the editor to create our own custom
functions.

Downloading HeidiSQL

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:

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.

Create the Help Database

Download the help database.

To import the database tables:

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

click the “Load SQL File...”

That will open
the file in a new Query editor window.

2. 
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:

the database contains four tables

Some Useful MySQL Functions

Before going off
and whipping up your own custom functions, be sure to check if what you need is
already covered by the many excellent native functions supported by MySQL. It’s
always better to use these first, as they are already tested for you and
require very little effort to insert in your SQL statements or stored procs.
Here are some standouts:

Concat

The CONCAT function is used
to concatenate two or more strings to form a single string. Its signature is
simple:


CONCAT(str1,str2,…);

Here’s an example that
produces a user ID:


SELECT CONCAT(name, dob_year, ‘_999’) as ID
FROM user_data;

Produces:

Rick1978_999
Jaimie1980_999
Sandra1967_999

CONCAT_WS()

CONCAT_WS stands for
Concatenate With Separator and is a special form of CONCAT(). The first
argument is the separator for the rest of the arguments. The separator is added
between the strings to be concatenated:


CONCAT_WS(separator,str1,str2,…)

One good use for it is for
formatting names. For example, it is common to display names starting with the
last name. Rather than send both the first and last names as separate fields,
we can do the work in one fell swoop by using the CONCAT_WS function:


SELECT CONCAT_WS(‘, ‘, last_name, first_name) AS name
FROM CLIENTS

Would return something like:

Gravelle, Rob
Perinbam, John
Nesrallah, George
Mercer, Steve

LOWER(str), UPPER(str)

Returns the string with all
characters changed to lower or upper case according to the current character
set mapping:


LOWER(str); / UPPER(str);

These functions are useful
when comparing strings whose case is uncertain. For instance, say we wanted to
retrieve business commands from a table that match the ‘SEND’ command.
However, we are unsure that commands are all in uppercase. We can use the
UPPER function to convert the contents of that field when making the
comparison:


SELECT *
FROM commands
WHERE UPPER(cmd_desc) = ‘SEND’;

LTRIM(str), RTRIM(str), TRIM(str)

Returns the string str with either
the leading space (LTrim), trailing space (RTrim), or both (Trim) characters
removed. The latter, Trim function offers by far the most flexibility in that,
not only can it perform the same actions as the LTrim and Rtrim functions, but it
can remove other characters besides spaces. Here are two alternate signatures
for the Trim function:


TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

TRIM([remstr FROM] str)

Returns the string str with
all remstr prefixes or suffixes removed. If none of the specifiers BOTH,
LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not
specified, spaces are removed.

EXAMPLE 1 – LTRIM:


SELECT LTRIM(‘ Doheny Dr.’);

Returns:

‘Doheny Dr.’

EXAMPLE 2 – RTRIM:


SELECT RTRIM(‘ Michigan State ‘);

Returns:

‘ Michigan State’

EXAMPLE 3 – TRIM:


SELECT TRIM(‘ No spaces for me. ‘);

Returns:

‘No spaces for me.’

SELECT TRIM(LEADING ‘x’ FROM ‘xxx22 Acacia Ave.xxx’);

Returns:

’22 Acacia Ave.xxx’

SELECT TRIM(BOTH ‘x’ FROM ‘xxx22 Acacia Ave.xxx’);

Returns:

’22 Acacia Ave.’

IN

You can use IN function to
replace many OR conditions.


IN(arg1, arg2, arg3, …)


SELECT *
FROM accounts_t
WHERE account_no in ( 199, 205, 300, 506, 700 );

Replaces:

SELECT *
FROM accounts_t
WHERE account_no = 199
OR account_no = 205
OR account_no = 300
OR account_no = 506
OR account_no = 700;

The following select
statement retrieves descriptions from a table where the IDs match those found
in an unrelated table, where foreign key linking is not possible. The nested
select returns a list of IDs from the first table with the description of ‘site
poe’. Then the IN function is used to match the IDs of a second table to the
returned ID list:


SELECT desc
FROM vcode_values
WHERE code_value_id IN (select code_value_id
from vcode_table_data
where LOWER(description) = ‘site poe’)

ROUND(X,[D])

This function returns X rounded to
the nearest integer. If a second argument, D, is supplied, then the function
returns X rounded to D decimal places. D must be positive or all digits to the
right of the decimal point will be removed. Here’s some examples:


SELECT ROUND(5.693893);

Returns:

5

SELECT ROUND(5.693893, 2);

Returns:

5.69

Creating Custom Functions Using the Stored Routine Editor

Custom functions
are a great way to group common calculations and expressions together. As with
programming languages, functions help reduce redundant code and improve
readability. It’s easy to create your own function is HeidiSQL using the
Stored Routine Editor:

1. 
Bring up the editor by
right-clicking the help database icon and selecting “Create stored routine …”
from the popup menu:

Bring up the editor

2. 
Enter “isEmpty” in the
Name field.

3. 
Select “Function” from
the Type list.

4. 
Select “Tinyint(1)”
from the Returns list.

5. 
Select “No SQL” from
the Data access list.

6. 
Leave the SQL Security
as “Definer”.

7. 
Add a parameter called
“param_name” with a Datatype of “VARCHAR(25) ” (don’t forget the space at
the end)
. Note that the Context cannot be changed from “IN” for functions.

8. 
Add the following code
in the Routine body:


BEGIN
DECLARE isEmpty TINYINT;
SET isEmpty =(param_name IS NULL or char_length(TRIM(param_name))=0);
RETURN isEmpty;
END

Here is the Stored routine editor with all the fields filled in:

the Stored routine editor

This function
determines whether or not a parameter was supplied to a stored procedure. The isEmpty
variable is declared as a tynyint, set to an expression that uses the IS NULL
clause and two native functions to check for empty strings, and returned from
the function.

9. 
Click OK to close the
dialog and create the function.

Our new function
can now be called from SQL statements like any other function:


IF isEmpty(keyword) THEN
#code not shown
ELSEIF isEmpty(topic) THEN
#more code

That concludes our series on
HeidiSQL Stored procedures editor. From here on in, we’ll be getting back to
MySQL and general database topics.

»


See All Articles by Columnist
Rob Gravelle

Robert Gravelle
Robert Gravelle
Rob Gravelle resides in Ottawa, Canada, and has been an IT guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.

Latest Articles