Mysql Function Return

Question

I have a Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertRaffleTicket`(IN userId BIGINT, IN raffleId BIGINT)
BEGIN
    INSERT INTO RaffleTicket(UserId, RaffleId)
    SELECT userId, raffleId
    FROM RaffleStatus rs
    WHERE rs.RaffleId = raffleId
    AND rs.SoldOut = 0;
END

How can I turn this into a function, that will return a value upon successful Insertion into the table?

I have tried:

CREATE DEFINER=`root`@`localhost` FUNCTION `addTicket`(UserId int, RaffleId int) RETURNS int(11)
BEGIN
    INSERT INTO RaffleTicket(UserId, RaffleId)
    SELECT userId, raffleId
    FROM RaffleStatus rs
    WHERE rs.RaffleId = raffleId
    AND rs.SoldOut = 0;
    return rs.SoldOut;
RETURN 1;
END

But it is giving me an Error 1109 Unkown table 'rs' in field list.

Hitesh · Accepted Answer · 2013-12-11 09:25:31Z

1

CREATE DEFINER=`root`@`localhost` FUNCTION `InsertRaffleTicket`(
userId BIGINT,
raffleId BIGINT
) RETURNS int
BEGIN

DECLARE retValue int;

select SoldOut into retValue from RaffleStatus where RaffleId = raffleId;

INSERT INTO RaffleTicket(UserId, RaffleId)
SELECT userId, raffleId
FROM RaffleStatus rs
WHERE rs.RaffleId = raffleId
AND rs.SoldOut = 0;

RETURN retValue;

END

edited Dec 11, 2013 at 9:25

answered Dec 11, 2013 at 8:21

Hitesh

3,5068 gold badges43 silver badges61 bronze badges

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

7 Comments

Coffman34 Over a year ago

I might have updated while you were answering, but I keep getting Error 1054 Unkown column WbId when I put your example into my database.

Hitesh Over a year ago

I have updated my answer, please check now, I forgot to change variable name

Coffman34 Over a year ago

That now adds the correct ticket. However it always returns 1. I need it to return the SoldOut value. So I know if SoldOut = 0, it added the ticket, if SoldOut = 1, it did not add the ticket.

Hitesh Over a year ago

Ive updated the code for the soldout output, please check now

Coffman34 Over a year ago

Error 1415: Not allowed to return a result set from a function. I should add, RaffleStatus will return all raffle's that do not have all tickets sold for the raffle. IE: If RaffleId 1 and RaffleID 2 have 12 tickets in each raffle, and they have only sold 10 each, RaffleStatus will return both.

|

Collectives™ on Stack Overflow

Mysql Function Return

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related