CODEWITHSUNDEEP
mysql
Coffman34
Coffman34

Reputation: 57

Mysql Function Return

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.

Upvotes: 0

Views: 75

Answers (1)

Hitesh
Hitesh

Reputation: 3498

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

Upvotes: 1

Related Questions