How do I call a MySQL stored procedure from an ASP application and get the
value of an Out Parameter?
I've got the following Stored Procedure defined in my database:
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(CONCAT('000000',cInvNo),6));
END IF;
IF nChapterID = -2 THEN
Set cInvNo = CONCAT('C',Right(CONCAT('000000',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(CONCAT('000000',cInvNo),6));
UPDATE Chapters SET TempInvNo=cInvNo WHERE ID=nChapterID;
END IF;
END IF;
END $$
DELIMITER ;
I've currently got the following ASP (VBScript) code, which worked with a
Microsoft SQL database, but does not work with the MySQL Database:
function GetNextInv(nChapterID)
Dim adocmd
Set adocmd = Server.CreateObject("ADODB.Command")
adocmd.CommandText = "sp_GetNextInv"
adocmd.ActiveConnection = Conn
adocmd.CommandType = adCmdStoredProc
adocmd.Parameters.Append adocmd.CreateParameter("ChapterID", adInteger,
adParamInput, 16, nChapterID)
adocmd.Parameters.Append adocmd.CreateParameter("InvNo", adVarChar,
adParamOutput,7)
adocmd.Execute
GetNextInv = adocmd.Parameters("InvNo").Value
set adocmd=Nothing
end function
I know that the proper way to call this function from the MySQL command line
would be sp_GetNextInv(1234,@cInvNo), but don't know how to do this in ASP.
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
