I have been here for hours and hours trying to work this out and have no more ideas where to take it... The closest I have got is as you can see below: But stuck with limit due to not being able to feed it a value....


Code:
CREATE DEFINER=`xxx` FUNCTION `ballmatch`(pBall1 INT, pBall2 INT, pBall3 INT, pBall4 INT, pBall5 INT, pBall6 INT, pBonusBall INT) RETURNS int(11)
BEGIN
DECLARE xBall1 int(11);
DECLARE xBall2 int(11);
DECLARE xBall3 int(11);
DECLARE xBall4 int(11);
DECLARE xBall5 int(11);
DECLARE xBall6 int(11);
DECLARE xBonusBall int(11);
DECLARE BallCount int(11);
SET BallCount=0;

SET xBall1 = (SELECT Ball1 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall2 = (SELECT Ball2 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall3 = (SELECT Ball3 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall4 = (SELECT Ball4 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall5 = (SELECT Ball5 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall6 = (SELECT Ball6 FROM lottosites LIMIT 1 OFFSET 0);
SET xBonusBall = (SELECT BonusBall FROM lottosites LIMIT 1 OFFSET 0);

if xBall1 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall2 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall3 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall4 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall5 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall6 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBonusBall in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;

RETURN BallCount;
END

Last edited by shabbir; 14Sep2009 at 22:25.. Reason: Code blocks