Run a query in a MySQL stored procedure if a condition is true

Question

I am using MySQL database and trying to create a stored procedure. How can I make it so that if the result of query1 has no records, then it execute a different query?

Here is what I have so far:

/* CREATE DB */
CREATE DATABASE mydata;
use mydata;
/* TABLE */
CREATE TABLE mydata (
ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name                        VARCHAR(255) NOT NULL,
Value                       VARCHAR(255) NOT NULL
) ENGINE=InnoDB;
INSERT INTO mydata (Name, Value) VALUES ("testname", "testvalue");

/* STORED PROCEDURE */
delimiter //
CREATE PROCEDURE myproc(IN myTable VARCHAR(255), 
                        IN myValue VARCHAR(255), 
                        IN myValueTwo VARCHAR(255))
BEGIN
    SET @iTable=myTable;
    SET @iValue=myValue;
    SET @iValueTwo=myValueTwo;

    SET @query = CONCAT('SELECT Name FROM ', @iTable, 
        ' WHERE Value="', @iValue, '"');
    SET @querytwo = CONCAT('SELECT Name FROM ', @iTable, 
        ' WHERE Value="', @iValueTwo, '"');
    PREPARE QUERY FROM @query;
    EXECUTE QUERY;

END //
delimiter ;

/* CALL */
call myproc("mydata", "testvalue", "");

I want to run a query, and execute a secondary query only if the first has no rows. What is the best way to do this?

is there a requirement that tables and insert statement should be dynamic? Using dynamic sql is not good approach. — rs., Mar 23 '12 at 20:05

score 4 · Accepted Answer · answered Mar 23 '12 at 21:06

This took some work but I made enough adjustments. The problem with your code has nothing to do with your logic but with MySQL Stored Procedure Language itself. When doing dynamic SQL It has scoping issues.

What I did was create a temp table and deposited the returned value in it

Here is some sample data loaded

mysql> drop database if exists user391986;
Query OK, 1 row affected (0.08 sec)

mysql> create database user391986;
Query OK, 1 row affected (0.00 sec)

mysql> use user391986
Database changed
mysql> CREATE TABLE mytable (
    -> ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name VARCHAR(255) NOT NULL,
    -> Value VARCHAR(255) NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO mytable (Name,Value) VALUES
    -> ('rolando','edge'),('pamela','washington'),
    -> ('dominique','wilkins'),('diamond','cutter');
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * from mytable;
+----+-----------+------------+
| ID | Name      | Value      |
+----+-----------+------------+
|  1 | rolando   | edge       |
|  2 | pamela    | washington |
|  3 | dominique | wilkins    |
|  4 | diamond   | cutter     |
+----+-----------+------------+
4 rows in set (0.00 sec)

mysql>

Here is the stored procedure adjusted to catch the return values in a temp table

mysql> delimiter //
mysql> CREATE PROCEDURE myproc(IN myTable VARCHAR(255), IN myValue VARCHAR(255), IN myValueTwo VARCHAR(255))
    -> BEGIN
    ->     DECLARE foundcount INT;
    ->     DECLARE retval VARCHAR(255);
    ->
    ->     SET @iTable=myTable;
    ->     SET @iValue=myValue;
    ->     SET @iValueTwo=myValueTwo;
    ->
    ->     CREATE TEMPORARY TABLE IF NOT EXISTS mynumber (rv VARCHAR(255)) ENGINE=MEMORY;
    ->     DELETE FROM mynumber;
    ->
    ->     SET retval = 'nothing retrieved';
    ->     SET @query = CONCAT('INSERT INTO mynumber SELECT Name FROM ', @iTable, ' WHERE Value=''', @iValue, '''');
    ->     PREPARE QUERY FROM @query;
    ->     EXECUTE QUERY;
    ->     DEALLOCATE PREPARE QUERY;
    ->     SELECT COUNT(1) INTO foundcount FROM mynumber;
    ->     IF foundcount = 0 THEN
    ->         SET @querytwo = CONCAT('INSERT INTO mynumber SELECT Name FROM ', @iTable, ' WHERE Value=''', @iValueTwo, '''');
    ->         PREPARE QUERY FROM @querytwo;
    ->         EXECUTE QUERY;
    ->         DEALLOCATE PREPARE QUERY;
    ->     END IF;
    ->     SELECT COUNT(1) INTO foundcount FROM mynumber;
    ->     IF foundcount > 0 THEN
    ->         SELECT rv INTO retval FROM mynumber;
    ->     END IF;
    ->     SELECT retval;
    ->
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>

OK I called the stored procedure three time. The first gets nothing. The second gets the second value. The third gets the first value.

mysql> CALL myproc('mytable','pamela','diamond');
+-------------------+
| retval            |
+-------------------+
| nothing retrieved |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> CALL myproc('mytable','pamela','wilkins');
+-----------+
| retval    |
+-----------+
| dominique |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL myproc('mytable','edge','wilkins');
+---------+
| retval  |
+---------+
| rolando |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

Give it a Try !!!

amazing thanks Rolando! With ENGINE=MEMORY; do I have to worry about deleting it manually at the end? Does the data get cleared after each execution or do I also have to take care of that? — user391986, Mar 23 '12 at 22:39
The TEMPORARY TABLE will clear itself whether the DB Connection disconnects normally or terminates unexpectedly. — RolandoMySQLDBA, Mar 28 '12 at 15:38

score 1 · Answer 2 · answered Oct 15 '18 at 02:19

My simplest working code

BEGIN
    IF test = 'null' THEN 
        PREPARE QUERY FROM 'SELECT username as name from login';
        EXECUTE QUERY;
    ELSE
        PREPARE QUERY FROM 'SELECT username as name2 from login';
        EXECUTE QUERY;
    END IF;
END

score 1 · Answer 3 · edited Dec 06 '13 at 21:57

1

In Sql Server, you could run the results into a temporary table and then check the temp table for rows like this:

declare @numberResults int = 0

create table #MyTempTable(...)

insert #MyTempTable
sp_executesql Query

set @numberResults = (select count(*) from #MyTempTable)

if @numberResults = 0
 begin
  sp_executesql secondQuery
 end

edited Dec 06 '13 at 21:57

Eric Leschinski

146,994
96
417
335

answered Mar 23 '12 at 19:45

rlobban

343
1
7

score 1 · Answer 4 · edited Dec 06 '13 at 21:55

1

In mysql you can use the found_rows() built in procedure like this:

el@apollo:~$ mysql -u root -p
Enter password: 
mysql> use your_database;
Database changed
mysql> select id from problems;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

In mssql you can use the value @@rowcount. Then you can run the second query only if the first query returned no rows:

EXECUTE QUERY;

IF @@rowcount = 0
BEGIN
     PREPARE QUERY FROM @querytwo;     
     EXECUTE QUERY;
END

edited Dec 06 '13 at 21:55

Eric Leschinski

146,994
96
417
335

answered Mar 23 '12 at 20:03

Webguy

51
3

I keep getting ERROR 1193 (HY000): Unknown system variable 'rowcount' – user391986 Mar 23 '12 at 20:08
MySql Doesnt have @@rowcount like sql server, you can find the equivalent here http://stackoverflow.com/questions/2229218/what-is-mysql-version-of-rowcount – dmportella Mar 24 '12 at 16:45
The main thing to use is Found_Rows() which you can find the documentation here http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows – dmportella Mar 24 '12 at 16:51

Run a query in a MySQL stored procedure if a condition is true

4 Answers4

Linked