Web Collections

What are Stored Procedures

MySQL 5.0 finally introduces functionality for Stored Procedures. So what exactly are stored procedures? That is the kind of question that gets database professionals who use other DBMS’s raising their eyebrows. Stored procedures have been integral to Oracle, PostgreSQL, DB-2, MS-SQL server and others for years, and it has long been a sore point that MySQL has not had them. But there is no snobbery here – if you are a MySQL newbie, or have been using MySQL for years and want to find out what all the fuss is about, read on. If it is your eyebrows that are raised, and you just want to know how MySQL implements them, you will be relieved to know MySQL stored procedures are very similar to the DB2 implementation, as both are based on the SQL:2003 standard.

A stored procedure is simply a procedure that is stored on the database server. MySQL developers have to date unthinkingly written and stored their procedures on the application (or web) server, mainly because there hasn’t been an option. That has been limiting. Some have claimed that there are two schools of thought – one claiming that logic should be in the application, the other saying it should reside in the database. However, most professionals would not bind themselves to one or other viewpoint at all times. As always, there are times when doing either makes sense. Unfortunately, some of the staunchest adherents of the in the application school are only there because until now they have had no choice, and it is what they are used to doing. So why would we want to place logic on the database server?Why use stored procedures?
They will run in all environments, and there is no need to recreate the logic. Since they are on the database server, it makes no difference what application environment is used – the stored procedure remains consistent. If your setup involves different clients, different programming languages – the logic remains in one place. Web developers typically make less use of this feature, since the web server and database server are usually closely linked. However, in complex client-server setups, this is a big advantage. The clients are automatically always in sync with the procedure logic as soon as its been updated.
They can reduce network traffic. Complex, repetitive tasks may require getting results, applying some logic to them, and using this to get more results. If this only has to be done on the database server, there is no need to send result sets and new queries back and forth from application server to database server. Network traffic is a common bottleneck causing performance issues, and stored procedures can help reduce this. More often though, it is the database server itself that is the bottleneck, so this may not be much of an advantage.
A simple example

A stored procedure is simply some SQL statements. Almost any valid SQL can go inside a stored procedure, with a few exceptions, which we will look at, at a later date. Let’s set up a basic stored procedure first. This one will simply say ‘Hello’ in the Xhosa language – Molo.
mysql> CREATE PROCEDURE molo() SELECT ‘Molo’;
Query OK, 0 rows affected (0.00 sec)

It is as simple as that. And to call it:
mysql> CALL molo()\G
*************************** 1. row ***************************
Molo: Molo
1 row in set (0.00 sec)

Hardly useful, but the basics are there. CREATE PROCEDURE sp_name() will define the procedure, and CALL sp_name() will call the procedure.
Parameters

The real benefit of a stored procedure is of course when you can pass values to it, as well as receive values back. The concept of parameters should be familiar to anyone who has had experience with any procedural programming experience.

There are three types of parameter:
IN: The default. This parameter is passed to the procedure, and can change inside the procedure, but remains unchanged outside.
OUT: No value is supplied to the procedure (it is assumed to be NULL), but it can be modified inside the procedure, and is available outside the procedure.
INOUT: The characteristics of both IN and OUT parameters. A value can be passed to the procedure, modified there as well as passed back again.

Mastery of stored procedures does require knowledge of session variables. Most of you probably know how to use session variables already, but if not, the concept is simple. You can assign a value to a variable, and retrieve it later. Here is an example, setting the variable x to the Xhosa word for hello to a group of people.
mysql> SET @x=’Molweni’;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molweni
1 row in set (0.00 sec)
An IN example

Here is an example of a stored procedure demonstrating the use of an IN parameter. Since IN is the default, there is no need to specify the parameter as such.
mysql> CREATE PROCEDURE sp_in(p VARCHAR(10)) SET @x = P;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_in(‘Molo’);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molo
1 row in set (0.00 sec)

The session variable @x is set inside of the procedure, based upon the parameter P, which is passed to the procedure, and remains unchanged.
An OUT example
mysql> SET @x=’Molweni’;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE sp_out(OUT p VARCHAR(10)) SET P=’molo’;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_out(@x);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G
*************************** 1. row ***************************
@x: molo
1 row in set (0.00 sec)

We reset @x just to make sure the final result is not a legacy of the previous procedure. This time, the parameter P is changed inside of the procedure, while the session variable is passed to the procedure, ready to receive the result.
An INOUT example
mysql> CREATE PROCEDURE sp_inout(INOUT P INT) SET @x=P*2;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_inout(2);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G
*************************** 1. row ***************************
@x: 4
1 row in set (0.00 sec)

Here, a parameter is passed to the procedure, used in the calculation, and the results are made available to the session variable @x.

Getting information about existing stored procedures

It is clearly necessary to be able to get more information about any stored procedures later, such as a list of procedures available, and the definitions. There are MySQL-specific ways to do this, and the syntax should be familiar to experienced MySQL users. SHOW PROCEDURE STATUS returns a list of stored procedures, and some metadata about them, while SHOW CREATE PROCEDURE returns the definition of a particular procedure.
mysql> SHOW PROCEDURE STATUS\G
************* 1. row ************
Db: test
Name: molo
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-07-29 19:20:27
Created: 2005-07-29 19:20:27
Security_type: DEFINER
Comment:
************* 2. row ************
Db: test
Name: sp_in
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-08-02 11:58:34
Created: 2005-08-02 11:58:34
Security_type: DEFINER
Comment:
************* 3. row ************
Db: test
Name: sp_inout
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-08-02 12:16:18
Created: 2005-08-02 12:16:18
Security_type: DEFINER
Comment:
************* 4. row ************
Db: test
Name: sp_out
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-08-02 12:01:56
Created: 2005-08-02 12:01:56
Security_type: DEFINER
Comment:
4 rows in set (0.00 sec)

It will become clear what all these fields mean as we progress through the rest of this tutorial series.
mysql> SHOW CREATE PROCEDURE molo\G
*************************** 1. row ***************************
Procedure: molo
sql_mode:
Create Procedure: CREATE PROCEDURE ‘test’.’molo'()
SELECT ‘Molo’
1 row in set (0.00 sec)

There is also an ANSI-standard way of doing it, which will be more familiar to other users.
mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G
*************************** 1. row ***************************
SPECIFIC_NAME: molo
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: molo
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SELECT ‘Molo’
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-07-29 19:20:27
LAST_ALTERED: 2005-07-29 19:20:27
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
*************************** 2. row ***************************
SPECIFIC_NAME: sp_in
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: sp_in
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x = P
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-08-02 11:58:34
LAST_ALTERED: 2005-08-02 11:58:34
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
*************************** 3. row ***************************
SPECIFIC_NAME: sp_inout
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: sp_inout
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x=P*2
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-08-02 12:16:18
LAST_ALTERED: 2005-08-02 12:16:18
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
*************************** 4. row ***************************
SPECIFIC_NAME: sp_out
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: sp_out
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET P=’molo’
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-08-02 12:01:56
LAST_ALTERED: 2005-08-02 12:01:56
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
4 rows in set (0.01 sec)

Let’s introduce some more complex examples. First, we will create a sample table.

mysql> CREATE table sp1 (id INT, txt VARCHAR(10), PRIMARY KEY(id));
Query OK, 0 rows affected (0.11 sec)

Delimiters, and multi-statement procedures

Stored procedures of course are not that useful if they are just one statement. The effects of all the procedures we have looked at so far could have had been duplicated much more easily with a single SQL statement. Useful procedures are much longer than that. Those of you who are on the ball may be thinking of a complication. How can we differentiate between multiple statements inside the procedure, and the end of the procedure? We have to create a different delimiter to end the CREATE PROCEDURE statement. Here is how:
mysql> DELIMITER |

Note that there is no semicolon after the ‘|’ symbol, which we will use as the delimiter for our purposes. You have to choose a delimiter that does not appear in your procedure, and it can be more than one character.

mysql> CREATE PROCEDURE sp_ins (P VARCHAR(10))
-> BEGIN
-> SET @x=CHAR_LENGTH(P);
-> SET @y = HEX(P);
-> INSERT INTO sp1(id,txt) VALUES(@x,@y);
-> END|
Query OK, 0 rows affected (0.05 sec)

mysql> CALL sp_ins(‘ABC’);
-> |
Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
id: 3
txt: 414243
1 row in set (0.00 sec)

Note what happened when we tried to call the procedure. Because MySQL was still using the | symbol as a delimiter, and not the semicolon, the statement did not run after the semicolon. We first needed to close it with the piping symbol. Afterwards, we reset the delimiter back to normal, and test that the records were correctly added to the sp1 table.
Procedure variables

Stored procedures do not only make use of the standard SQL statements. You can also DECLARE variables that exist only inside the procedure, as well as assign values to them with the SET statement without using the ‘@’ symbol, required for session variables. Here is an example.
mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_declare (P INT)
-> BEGIN
-> DECLARE x INT;
-> DECLARE y INT DEFAULT 10;
-> SET x = P*y;
-> INSERT INTO sp1(id,txt) VALUES(x,HEX(‘DEF’));
-> END|
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL sp_declare(4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
id: 3
txt: 414243
*************************** 2. row ***************************
id: 40
txt: 444546
2 rows in set (0.00 sec)

Variables that are declared without a default, such as x above, are set to NULL.
Populating variables from a pre-existing table

Now that you have seen how to INSERT records into a table from within a procedure, you may be wondering how we get values out of an existing table. Here is a simple example.
mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_select ()
-> BEGIN
-> DECLARE x INT;
-> DECLARE y VARCHAR(10);
-> SELECT id,txt INTO x,y FROM sp1 LIMIT 1;
-> SELECT x,y;
-> END|
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL sp_select()\G
*************************** 1. row ***************************
x: 3
y: 414243
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Author: Ian Gilfillan

1. Fully Qualified Names – Always use the fully qualified name when calling stored procedures. This would be the format database_name.schema_name.table_name.

For example, use EXEC master.dbo.Your_Proc_name instead of EXEC Your_Proc_name This is a very common mistake, which causes an extra trip to the procedure cache to get the execution plan for execution. Also try to use the schema name while creating a procedure. Like: CREATE PROCEDURE dbo.Your_Proc_name instead of CREATE PROCEDURE Your_Proc_name

2. The sp_ prefix – Don’t use the “sp_” prefix in a stored procedure name as the “sp_” prefix is reserved for system stored procedures. Any stored procedure that has the “sp_” prefix will cause an extra lookup in the MASTER database If a stored procedure uses same name in both the user database and a system database, the stored procedure in the user database will never get executed.

3. Keywords – Use SQL keywords in capital letters to increase readability. Also use proper indentation to increase readability.

4. SET NOCOUNT ON – This returns the message that shows number of rows affected by SQL statement. This can cause extra network traffic and can have some serious impact on performance when the procedure is called frequently.

5. sp_executeSQL and the KEEPFIXED PLAN options – Both sp_executesql and the KEEPFIXED PLAN option avoid the recompilation of a stored procedure. If you want to provide parameterized dynamic SQL, then go for sp_executesql instead of EXEC(proc_name). Here the execution plan for the procedure is stored with the variable name in cache memory. When the variable values are supplied, then the values are simply mapped to the query, hence no need for a recompilation.

6. SELECT vs SET – A single SELECT statement can assign values to different variables and is much faster than multiple SET statements assigning values to multiple different variables.

Example:
SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 – 1
instead of

SET @Var1 = @Var1 + 1

SET @Var2 = @Var2 – 1

7.WHERE clauses – In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their performance.

=, >, <, >=, <=, <>, !=, !>, !<

8.CAST and CONVERT – Try to use CAST instead of CONVERT. CAST is ANSI-92 standard but CONVERT works in MS SQL server only. Also, Convert may be deprecated in future MS SQL releases. It is better to use CONVERT only when you need to format the DATETIME datatype with the style option. CAST cannot do this.

9.Avoid DISTINCT and ORDER BY – If you don’t need the DISTINCT/ORDER BY clause, then try to avoid so. Unnecessary DISTINCT or ORDER BY clauses cause extra work for the database engine. Hence making performance slower.

10.Avoid using cursors – Try to use temporary table/table variables with identity column and then iterate all the tables using WHILE loop and a looping counter, which will map with the identity column.

11.SELECT statements – Try to use only the required number of columns in the SELECT clause instead of using *. Using * returns all columns, which unnecessarily create a fat recordset.

12.Try to use table variables instead of Temporary Tables – Temp tables can cause stored procedures to recompile. But table variables were designed specifically to guard against stored procedure recompiles during execution.

13.Try to use table variables instead of Temporary Tables – Temp tables can cause stored procedures to recompile. But table variables were designed specifically to guard against stored procedure recompiles during execution.

If the result set is not containing a huge number of records then you should stick to table variable, otherwise temp table has its advantages. There is a misconception that temp tables always use the tembdb database but table variable do not. Table variables also use tempdb after a certain size.

14.Subquery vs JOINs -In fact most sub queries can be expressed as an equivalent form of JOIN. subquery is faster when we have to retrieve data from large number of tables because it becomes tedious to join more tables. JOIN is faster to retrieve data from database when we have less number of tables. But try to avoid correlated sub queries because it makes the query much slower.

15.Use proper indexes – You can use the help of the data tuning advisor, but it does not gives the proper result all the time. Index scans are much faster than table scans. So identify the table scans from the execution plans. But when a table returns smaller rows, then it is better to use a table scan.