MySql stored procedure rows issue-CodePudding

I have this stored procedure:

    CREATE DEFINER=`admin`@`%` PROCEDURE `GetTickets4Card`(
        IN p_TicketID int, 
        OUT p_returnvalue int
    )

BEGIN

    SELECT idbookingstickets
        INTO @p_returnvalue
        FROM bookingstickets 
        WHERE TicketId = p_TicketID;
        
        /* Return value accordingly */
        IF mysqll_affected_rows = 0 THEN  SET p_returnvalue = 0;
        /*
        ELSE
            SELECT * FROM BookingsTicketsCollected WHERE p_returnalue = idtickets;
            if mysqll_affected_rows = 0 THEN SET p_returnvalue = -1;
            END IF;
            */
        END IF;
    END

It gives me the following error: "Result consisted of more than one row". It may have something to do with mysql_affected_rows , but I have no idea, I want to know if the sql statement returns 1 row or not, any ideas?

Call code:

set @p_returnvalue = 0;
call yourTICKETbox_LIVE_DB.GetTickets4Card("aabb188e-6adc-11e5-9770-061de6653ea3", @p_returnvalue);
select @p_returnvalue;

CodePudding user response：

When you use SELECT ... INTO variable, the query must return at most one row. If you only care whether there are any matching rows, you can use the EXISTS() function.

SET p_returnvalue = EXISTS(
    SELECT 1
    FROM bookingstickets 
    WHERE TicketId = p_TicketID);

BTW, the MySQL equivalent to the PHP function mysqli_affected_rows() is ROW_COUNT().