Mysql stored procedure multiple selects

Question

I am running a stored procedure. The issue seems to be that it will go into the if statement. Also for some reason or another regardless of how many selects I use it will only return the first. I've copied this from another stored procedure that works like a charm, but this one just won't go. Any ideas?

DROP PROCEDURE IF EXISTS genSelPriceTier;
DELIMITER $$
CREATE PROCEDURE genSelPriceTier(tier_id INT, default_id INT)
    BEGIN
       DECLARE rowCount INT DEFAULT 0;  
          SELECT * FROM price_tier WHERE price_tier_id = tier_id;
          SET rowCount = FOUND_ROWS();
        IF rowCount < 1 THEN
            SELECT * FROM price_tier WHERE price_tier_id = default_id;
            END IF;
    END$$
DELIMITER ;

Korhan Ozturk · Accepted Answer · 2012-01-27 20:29:50Z

1

There is a bug reported related to the usage of FOUND_ROWS(). So, I recommend using Count(*) for the number of rows returned. Something like the following should work.

DROP PROCEDURE IF EXISTS genSelPriceTier;
DELIMITER $$
CREATE PROCEDURE genSelPriceTier(tier_id INT, default_id INT)
    BEGIN
       DECLARE rowCount INT DEFAULT 0;  
          SELECT COUNT(*) INTO rowCount FROM price_tier WHERE price_tier_id = tier_id 
        IF rowCount < 1 THEN
            SELECT * FROM price_tier WHERE price_tier_id = default_id;
            END IF;
    END$$
DELIMITER ;

edited Jan 27, 2012 at 20:29

answered Jan 27, 2012 at 19:32

Korhan Ozturk

11.3k6 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Bret Thomas Over a year ago

Yes certianly less than 1 or rowCount = 0 would work, but sorry to say this answer does not work either. Very perplexed

Bret Thomas Over a year ago

There is no error, it just doesn't go into the If statement and return the second select if nothing is found in the first select e.g. if rowCount returns a value les than 1

Bret Thomas Over a year ago

Looking at it more it must be returning a value higher than 1 in the found rows. I only have 2 recored in the DB id 1 and 2 respectively. so if I pass (0,2) i should return record 2 as the default but it does not

Korhan Ozturk Over a year ago

I've edited my answer - added a limit for no rows to be returned which might be required for FOUND_ROWS() to work correctly. Can you try this?

Bret Thomas Over a year ago

I have and still no dice. Strange thing is i'm running the following in another stored procedure and it works SELECT hit_date FROM hit_counter WHERE page = this_page AND site = this_site AND hit_date = CURDATE(); SET rowCount = FOUND_ROWS(); IF rowCount < 1 THEN INSERT INTO hit_counter (hit_date, site, page, hits) VALUES (CURDATE(), this_site, this_page, 1); ELSE

|

Collectives™ on Stack Overflow

Mysql stored procedure multiple selects

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related