MySQL – stored procedures and dynamic SQL

[adsense id=”0514458240″ width=”468″ height=”60″]

This is something that appears but is not solvable using standard SQL. For example, let us imagine that we want to return a set of rows belonging to a set of values. To be more concrete, imagine we have a table of books (defined with bookid and title) and a set of users (uid and username) and booksread (bookid, uid). We want to return a list of persons who has read a particular book or books. The last part is particularly important it may be one or more books. We could do this through our programming language whether PHP, C, C++, Java or Perl where we dynamically create the SQL before submitting it. However, in some cases we are using a tool which permits execution of a stored procedure but not the means of generating SQL. In this case we could use stored procedure and dynamically generated SQL from within the stored procedure based on input parameters.

From our example, let us imagine that we want to return a set of users that have a read a particular books:

SELECT u.username 
  FROM users u, books b, booksread r
 WHERE u.uid = r.uid
   AND r.bookid = b.bookid
   AND b.title = 'abc';

This is simple as we have a single value no dynamic SQL is required to resolve this issue. However, what if we have a list of books, such as: ‘abc’,’photography’,’video’ but this list is not restricted to those entries. There may be one or more different entries. In this case the SQL above is not sufficient. We need to generate the SQL dynamically as follows:

SELECT u.username, b.title
  FROM users u, books b, booksread r
 WHERE u.uid = r.uid
   AND r.bookid = b.bookid
   AND b.title IN ( listofbooks );

Where listofbooks is replaced by the following string “‘abc’,’photography’,’video’”.

So we will created a stored procedure that will accept a string list of books:

DROP PROCEDURE IF EXISTS sp_books;
DELIMITER $$
CREATE PROCEDURE sp_books(IN listOfBooks VARCHAR(100))
BEGIN
  SET @query := CONCAT("SELECT u.username, b.title FROM users u, books b, booksread r WHERE u.uid = r.uid AND r.bookid = b.bookid AND b.title IN (", listOfBooks, " )");
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;
$$

Lets have a look at this in action:

mysql> create table books(bookid int auto_increment primary key, title varchar(100));
Query OK, 0 rows affected (0.06 sec)

mysql> create table users(uid int auto_increment primary key, username varchar(30));
Query OK, 0 rows affected (0.07 sec)

mysql> create table booksread(uid int, bookid int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into users(username) values ('Tom'),('Dick'),('Harry'),('Jerry'),('Frank'),('Ron'),('Bud');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> insert into books(title) values ('abc'),('photography'),('video'),('soccer'),('football'),('tennis'),('golf'),('fishing'),('cycling');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> insert into booksread(uid, bookid) values (1,2),(1,5),(2,4),(3,2),(3,3),(3,4),(3,5),(5,1),(5,5);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

If we use the SQL above we will get back:

mysql> SELECT u.username, b.title
    ->   FROM users u, books b, booksread r
    ->  WHERE u.uid = r.uid
    ->    AND r.bookid = b.bookid
    ->    AND b.title IN ( 'abc','photography','video');
+----------+-------------+
| username | title       |
+----------+-------------+
| Tom      | photography | 
| Harry    | photography | 
| Harry    | video       | 
| Frank    | abc         | 
+----------+-------------+
4 rows in set (0.00 sec)

So when creating and executing the stored procedure we would expect similar results:

mysql> CREATE PROCEDURE sp_books(IN listOfBooks VARCHAR(100))
    -> BEGIN
    ->   SET @query := CONCAT("SELECT u.username, b.title FROM users u, books b, booksread r WHERE u.uid = r.uid AND r.bookid = b.bookid AND b.title IN (", listOfBooks, " )");
    ->   PREPARE stmt FROM @query;
    ->   EXECUTE stmt;
    ->   DEALLOCATE PREPARE stmt;
    -> END;
    -> $$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call sp_books( "'abc','photography','video'" );
+----------+-------------+
| username | title       |
+----------+-------------+
| Tom      | photography | 
| Harry    | photography | 
| Harry    | video       | 
| Frank    | abc         | 
+----------+-------------+
4 rows in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

There are some things to note:

• The list of entries passed into the stored procedure must contain a surrounding “” with the strings inside containing a single quote ” or escape the double quotes inside the quotes.
• If you attempt to declare a local varchar so store the query used in the PREPARE statement this will fail. This should be stored inside a user defined session variable i.e. @query.