Re: Please Help - Stored Procedure Issue
Aaah. OK. That's what the @ stands for. I could not figure out what the @ was for, and I posted a message on the list yesterday and did not receive a response. You're right. That solved the problem. Thanks, Jesse - Original Message - From: Chris White [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 27, 2006 5:15 PM Subject: Re: Please Help - Stored Procedure Issue On Tuesday 27 June 2006 01:52 pm, Jesse wrote: @cInvNo replace all instances of this with just cInvNo. cInvNo is already declared as an OUT variable, and @cInvNo will be set to that value when you run: CALL sp_GetNextInv(-1,@cInvNo); -- Chris White PHP Programmer/DBBD Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please Help - Stored Procedure Issue
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('000' + 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]
Re: Please Help - Stored Procedure Issue
On Tuesday 27 June 2006 01:52 pm, Jesse wrote: @cInvNo replace all instances of this with just cInvNo. cInvNo is already declared as an OUT variable, and @cInvNo will be set to that value when you run: CALL sp_GetNextInv(-1,@cInvNo); -- Chris White PHP Programmer/DBBD Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedure issue.
I'm having some issues creating a stored procedure to optimize tables
in the database. I'm pulling the table names from the information
schema. The tablename doesn't seem to be correctly being replaced in
the optimize command..
I've tried used prepared statements which seem to correctly replace
the tableName, but I get an error that prepared statements do not
support that kind of query.
With this current version the error is: table queue.tableName doesn't exist..
Any thoughts from some SP gurus? Thanks.
--
DELIMITER $$;
DROP PROCEDURE IF EXISTS `queue`.`sp_OptimizeDatabase`$$
CREATE PROCEDURE `queue`.`sp_OptimizeDatabase` ()
BEGIN
DECLARE exitValue INT;
DECLARE tableName CHAR(120);
DECLARE cursorList CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables where
TABLE_SCHEMA = 'queue' AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET exitValue = 1;
OPEN cursorList;
REPEAT
FETCH cursorList INTO tableName;
OPTIMIZE TABLE tableName;
-- SET @optSQL := concat('OPTIMIZE TABLE ', tableName);
-- PREPARE pOptimize FROM @optSQL;
-- EXECUTE pOptimize;
-- DEALLOCATE PREPARE pOptimize;
UNTIL exitValue = 1
END REPEAT;
CLOSE cursorList;
END$$
DELIMITER ;$$
-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure issue.
I'm having some issues creating a stored procedure to optimize tables
in the database.
PREPARE accepts only CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT,
SET and UPDATE.
PB
-
DreamWerx wrote:
I'm having some issues creating a stored procedure to optimize tables
in the database. I'm pulling the table names from the information
schema. The tablename doesn't seem to be correctly being replaced in
the optimize command..
I've tried used prepared statements which seem to correctly replace
the tableName, but I get an error that prepared statements do not
support that kind of query.
With this current version the error is: table queue.tableName doesn't exist..
Any thoughts from some SP gurus? Thanks.
--
DELIMITER $$;
DROP PROCEDURE IF EXISTS `queue`.`sp_OptimizeDatabase`$$
CREATE PROCEDURE `queue`.`sp_OptimizeDatabase` ()
BEGIN
DECLARE exitValue INT;
DECLARE tableName CHAR(120);
DECLARE cursorList CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables where
TABLE_SCHEMA = 'queue' AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET exitValue = 1;
OPEN cursorList;
REPEAT
FETCH cursorList INTO tableName;
OPTIMIZE TABLE tableName;
-- SET @optSQL := concat('OPTIMIZE TABLE ', tableName);
-- PREPARE pOptimize FROM @optSQL;
-- EXECUTE pOptimize;
-- DEALLOCATE PREPARE pOptimize;
UNTIL exitValue = 1
END REPEAT;
CLOSE cursorList;
END$$
DELIMITER ;$$
-
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.7/259 - Release Date: 2/13/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
