MySQL Stored Procedure Only Returning One Select Query

Question

I have the following stored procedure:

CREATE PROCEDURE `Get_Events_And_Deadlines_By_User`(IN `intervalStart` INT, IN `intervalEnd` INT)
    BEGIN
        IF (intervalStart != 0 AND intervalEnd != 0) THEN
            SELECT 1 AS `test`;
        ELSEIF (intervalStart != 0 AND intervalEnd = 0) THEN
            BEGIN
                SELECT 2 AS `test`;
                IF ((SELECT FOUND_ROWS())=1) THEN
                    SELECT 3 AS `test`;
                END IF;
            END;
        ELSE
            SELECT 4 AS `test`;
        END IF;
    END

When I run call Get_Events_And_Deadlines_By_User(1,0) I only get the select query with 2 as the result, the select inside the if statement is never returned. It seems like only the first select query that is encountered is being executed before the stored procedure is returned. Why is this? What can I do to fix this? I want the select query INSIDE the if statement to be the ONLY result when the if holds.

Answer 1

Your procedure returns multiple resulsets.
PDO:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(
    PDO::ATTR_EMULATE_PREPARES=>false,
    PDO::MYSQL_ATTR_DIRECT_QUERY=>false,
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
));

$result = $pdo->query('Call Get_Events_And_Deadlines_By_User(1,0)');
do {
    foreach( $result as $row ) {
        echo join(', ', $row), "\r\n";
    }
    echo "-----\r\n";
} while($result->nextRowset());

Answer 2

My test:

mysql> DELIMITER $$

mysql> DROP PROCEDURE IF EXISTS `Get_Events_And_Deadlines_By_User`$$
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> CREATE PROCEDURE `Get_Events_And_Deadlines_By_User`(
    ->     IN `intervalStart` INT,
    ->     IN `intervalEnd` INT
    -> )
    -> BEGIN
    ->     IF (intervalStart != 0 AND intervalEnd != 0) THEN
    ->         SELECT 1 AS `test`;
    ->     ELSEIF (intervalStart != 0 AND intervalEnd = 0) THEN
    ->         SELECT 2 AS `test`;
    ->         IF ((SELECT FOUND_ROWS())=1) THEN
    ->             SELECT 3 AS `test`;
    ->         END IF;
    ->     ELSE
    ->         SELECT 4 AS `test`;
    ->     END IF;
    -> END$$
Query OK, 0 rows affected (0,00 sec)

mysql> DELIMITER ;

mysql> CALL `Get_Events_And_Deadlines_By_User`(1, 0);
+------+
| test |
+------+
|    2 |
+------+
1 row in set (0,00 sec)

+------+
| test |
+------+
|    3 |
+------+
1 row in set (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

After: Retrieving Multiple Result sets with stored procedure in php/mysqli.

Answer 3

Q: I only get the select query with 2 as the result, the select inside the if statement is never returned. It seems like only the first select query that is encountered is being executed before the stored procedure is returned. Why is this?

A: That second resultset is being returned by the procedure. The client is responsible for requesting the second (and subsequent) resultsets. (In PHP, that would require calling the mysqli mysqli_next_result, or the PDO nextRowset function, depending on which interface library you are using.

But that doesn't seem to be your real question.

Q: What can I do to fix this?

A: That really depends on what behavior you want to achieve. It's possible for a procedure to return multiple resultsets, and for the client to process them.

Q: I want the select query INSIDE the if statement to be the ONLY result when the if holds.

A: You want to a run a query (the SELECT 2 query in the example procedure) but you don't want the procedure to return that as a resultset. You just want to know how many rows that query returns, and to conditionally control the flow in your stored program based on how many rows are returned.

There are several constructs you can use to achieve that. It is possible to run a query within a procedure without having the result from that query returned as a resultset.

---

test for "more than zero" rows with SELECT EXISTS (subquery)

I suspect you don't really want to use the FOUND_ROWS function; and I suspect that you don't want to test whether the number of rows found is exactly equal to 1.

If what you are attempting to achieve is determining whether a particular query will return one or more rows, you could use a pattern like this:

      IF ( SELECT EXISTS ( SELECT 2 AS `test` ) ) THEN
         SELECT 2 AS `test`;
      ELSE
         SELECT 3 AS `test`;
      END IF;

Given that the queries in the example code are guaranteed to return exactly one row, I'm just guessing at what you are trying to achieve.

If you have query, and you want to see if that query returns a row, you can wrap it in an EXISTS() condition, which will return a boolean.

EXISTS(subquery) returns a value of 1 if the subquery returns at least one row; and returns 0 if the subquery doesn't return a row.

That construct can be used within an IF for controlling the logic flow within a stored program.

   IF ( SELECT EXISTS(subquery) ) THEN
      -- subquery returned at least one row, so do something
   ELSE
      -- subquery didn't return any rows, so do something else
   END IF;

---

get exact count of rows with SELECT COUNT(1) INTO FROM (subquery) q

If testing the "existence" of rows from a subquery isn't sufficient; if there's a reason you need to get an exact number of rows returned by a subquery, you could use a COUNT() aggregate. For example:

  SELECT COUNT(1) FROM (subquery) q

To avoid returning the result from that query as a resultset from the procedure, you can assign the value returned by that into procedure variable or a user-defined variable. Assuming you've declared a procedure variable at the top of the procedure, something like this:

   DECLARE myrowcount INT;

You could do something like this:

   SELECT COUNT(1) INTO myrowcount FROM (subquery) q
   IF ( myrowcount = 1 ) THEN 
      -- subquery returned exactly one row
   ELSE
      -- subquery either returned zero rows, or returned more than one row
   END IF;

A user-defined variable could be used in place of a procedure variable. My preference is to use a procedure variable, if there's no need to persist that count of rows beyond the execution of the procedure.

(The biggest downside to using a user-defined variable is that it introduces unnecessary ambiguity. Someone who is later reading the code is left wondering whether the value stored in the user-defined variable is actually needed after the procedure ends. They don't know whether that's a purposeful, intentional side effect of the procedure, that something else is dependent on. To my mind, avoiding that ambiguity is sufficient reason to use a procedure variable.)

Answer 4

Based on what you mentioned in comments so far, I think you are looking for something below. All I did was to add a new parameter as an output to your procedure and instead of using SELECT to fill the value of test, I have used a variable sending from outside to your function and receive the updated value from outside and use SELECT @test to get your value and it will be only one value at a time not two.

If you have questions regarding the comment I left in procedure, I suggest you read more about FOUND_ROWS() usage.

DELIMITER $$
DROP PROCEDURE IF EXISTS `Get_Events_And_Deadlines_By_User` $$

CREATE PROCEDURE `Get_Events_And_Deadlines_By_User`(
        IN `intervalStart` INT,
        IN `intervalEnd` INT,
        OUT `test` INT
    )
    BEGIN
        IF (intervalStart != 0 AND intervalEnd != 0) THEN
            SET test = 1;
        ELSEIF (intervalStart != 0 AND intervalEnd = 0) THEN
            BEGIN
                SET test = 2;
                # include your select query here, 
                # as you need if for FOUND_ROWS(), or
                # it will always return 1 cause there is no successful
                # SELECT query before it and it will be always 1. 
                SELECT * FROM someTable;
                IF ((SELECT FOUND_ROWS())=1) THEN
                    SET test = 3;
                END IF;
            END;
        ELSE
            SET test = 4;
        END IF;
    END$$
DELIMITER ;

CALL Get_Events_And_Deadlines_By_User(1,0,@test);
SELECT @test;

I hope it helps.