I have the following stored procedure that I'm using:
DELIMITER $$
DROP PROCEDURE IF EXISTS `bpa`.`sp_GetNextInv` $$
CREATE PROCEDURE `sp_GetNextInv`(
IN nChapterID Int,
OUT cInvNo VarChar(7))
BEGIN
Declare cPrefix VarChar(1);
Declare cNextInv VarChar(7);
Set cInvNo = '';
IF nChapterID > 0 THEN
SELECT TempInvNo INTO cInvNo FROM Chapters WHERE ID=nChapterID;
END IF;
IF (cInvNo = '') or (cInvNo IS NULL) THEN
SELECT NextInvoiceNo INTO cInvNo FROM Config;
SET cNextInv = Right('0000000' + CONVERT(CONVERT(cInvNo, UNSIGNED) + 1,
CHAR), 7);
UPDATE Config SET NextInvoiceNo=cNextInv;
IF nChapterID = -1 THEN
Set @cInvNo = CONCAT('L',Right(cInvNo,6));
# at this point, it's right.
END IF;
IF nChapterID = -2 THEN
Set @cInvNo = CONCAT('C',Right(cInvNo,6));
END IF;
IF nChapterID > 0 THEN
SELECT CT.InvPrefix INTO cPrefix FROM ChapterType CT, Chapters C
WHERE C.ID=nChapterID AND CT.ChapterType=C.ChapterType;
Set cInvNo = CONCAT(cPrefix,Right(cInvNo,6));
UPDATE Chapters SET TempInvNo=cInvNo WHERE ID=nChapterID;
END IF;
END IF;
END $$
DELIMITER ;
The function should select the next invoice #, and it should have a alpha
prefix assigned to it. To debug this, I put "SELECT @cInvNo;" in the code
above where it indicates "# at this point, it's right". It properly returns
'L65973'. however, in MySQL, I can do the following:
CALL sp_GetNextInv(-1,@cInvNo);
SELECT @cInvNo;
...and it returns 65973. Does anyone have a clue why it's doing that?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
