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.
>Solution :
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.