Mysql Question posted in
The official MySQL documentation can be found here.

Get the number of rows returned by a query in MySQL

Is there an easy way to get the number of rows returned by a query as well as the rows themselves. For example, in a stored procedure, I need to return rows from a query and, if no rows are being returned, I need to do additional processing.

SELECT * FROM MyTable;
IF [Number of rows] = 0 THEN
    [Do other stuff]
END IF;

The Proc still needs to return the contents of MyTable even if it doesn’t have any rows. I know this can be using two SELECT statements (a COUNT() to get the number of rows and a second to get the rows), but I’m wondering if there is a way to do this with a single SELECT.

Tags:

2

Answers


  1. 0

    No, not in SQL.

    The ROW_COUNT() function is for rows affected, not rows returned. It’s useful for DML like INSERT, UPDATE, DELETE, but it returns -1 for a SELECT query.

    Some client interfaces do have a rows-returned function, but this works only after the client has fetched a result set. A client doesn’t know how many rows will be returned until it fetches them.

    As you wrote, you can write a SELECT COUNT(*)... query first. That’s what I’d do.

    Login or Signup to reply.
  2. 0

    You could find this out with a cursor. You run the select query that you have and do a single fetch (see https://www.mysqltutorial.org/mysql-stored-procedure/sql-cursor-in-stored-procedures/) and if there was an element in this cursor, then it is not empty and proceed in that manner, otherwise it is empty.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search