CODEWITHSUNDEEP
mysqlcountdatabase-cursor
vishal
vishal

Reputation: 4083

mysql number of records in cursor without iterating?

I am trying to write mysql procedure for below logic,

select id, fullname from users where fullname like concat(lastname, ' ', firstname, ' (' , middlename, '%');

if above query returns 0 records then

    select id, fullname from users where fullname like concat(lastname, ' ', firstname, '%');

.... few more queries depending upon result,

I am trying to write procedure mysql procedure , in that, I am using mysql cursor,

DECLARE user_cnt CURSOR FOR select id, fullname from users where fullname like concat(lastname, ' ', firstname, ' (' , middlename, '%');

now I want to check number of records in user_cursor so that I can check the condition "if query returns 0 records then"

How can I find the number of records in mysql cursor without iterating on it ?

Upvotes: 10

Views: 22035

Answers (2)

Bnn
Bnn

Reputation: 1

Include SQL_CALC_FOUND_ROWS in your cursor select.Then on fetch use FOUND_ROWS(). It gives the number of records

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

Do a count before the cursor:

select count(*)
into @user_cnt
from users
where fullname like concat (
    lastname,
    ' ',
    firstname,
    ' (',
    middlename,
    '%'
    );

EDIT: If you want to do it after OPENING the cursor, try doing:

OPEN your_cursor;
select FOUND_ROWS() into user_cnt ;

Upvotes: 20

Related Questions