Description:
I am using windows version of mysql 5 and the server dies with fault when I declare variable in a stored procdure
Here is my sp:
DROP PROCEDURE IF EXISTS SP_INSERT_DNOTE_HEADER;
delimiter //
CREATE PROCEDURE SP_INSERT_DNOTE_HEADER (
IN in_cs_id VARCHAR(10),
IN in_inv_date DATE,
IN in_inv_remark TEXT
)
BEGIN
DECLARE inv_no VARCHAR(20);
SELECT CONCAT('DNE', LPAD(CONCAT(1 + IFNULL(SUBSTRING(H.INV_NO,4), 0), ''), 10, '0')) INTO inv_no
FROM TBL_DNOTE_HEADER H
ORDER BY H.INV_NO DESC LIMIT 1;
IF LENGTH(in_inv_no) <=0 THEN
SET in_inv_no = CONCAT('DNE', LPAD('1', 10, '0'));
END IF;
INSERT INTO TBL_DNOTE_HEADER (INV_NO, CS_ID, INV_DATE, INV_REMARK, DELETED) VALUES
(in_inv_no, in_cs_id, in_inv_date, in_inv_remark, 0);
END;
//
delimiter ;
It is successfully created. But when I use
call SP_INSERT_DNOTE_HEADER('AB001', NOW(), '');
The server program suddenly dies with error.
But when I remove the declare statment, it works smoothly.
So I force to use an extra input parameter as variable for my stored procedure.
Here is my working code
DROP PROCEDURE IF EXISTS SP_INSERT_DNOTE_HEADER;
delimiter //
CREATE PROCEDURE SP_INSERT_DNOTE_HEADER (
IN in_inv_no VARCHAR(20),
IN in_cs_id VARCHAR(10),
IN in_inv_date DATE,
IN in_inv_remark TEXT
)
BEGIN
SELECT CONCAT('DNE', LPAD(CONCAT(1 + IFNULL(SUBSTRING(H.INV_NO,4), 0), ''), 10, '0')) INTO in_inv_no
FROM TBL_DNOTE_HEADER H
ORDER BY H.INV_NO DESC LIMIT 1;
IF LENGTH(in_inv_no) <=0 THEN
SET in_inv_no = CONCAT('DNE', LPAD('1', 10, '0'));
END IF;
INSERT INTO TBL_DNOTE_HEADER (INV_NO, CS_ID, INV_DATE, INV_REMARK, DELETED) VALUES
(in_inv_no, in_cs_id, in_inv_date, in_inv_remark, 0);
END;
//
delimiter ;
call SP_INSERT_DNOTE_HEADER('', 'AB001', NOW(), '') works smoothly this time
Does anyone have the same problem that I faced? Any solutions?
How to repeat:
Having a declare statement in stored procedure
Description: I am using windows version of mysql 5 and the server dies with fault when I declare variable in a stored procdure Here is my sp: DROP PROCEDURE IF EXISTS SP_INSERT_DNOTE_HEADER; delimiter // CREATE PROCEDURE SP_INSERT_DNOTE_HEADER ( IN in_cs_id VARCHAR(10), IN in_inv_date DATE, IN in_inv_remark TEXT ) BEGIN DECLARE inv_no VARCHAR(20); SELECT CONCAT('DNE', LPAD(CONCAT(1 + IFNULL(SUBSTRING(H.INV_NO,4), 0), ''), 10, '0')) INTO inv_no FROM TBL_DNOTE_HEADER H ORDER BY H.INV_NO DESC LIMIT 1; IF LENGTH(in_inv_no) <=0 THEN SET in_inv_no = CONCAT('DNE', LPAD('1', 10, '0')); END IF; INSERT INTO TBL_DNOTE_HEADER (INV_NO, CS_ID, INV_DATE, INV_REMARK, DELETED) VALUES (in_inv_no, in_cs_id, in_inv_date, in_inv_remark, 0); END; // delimiter ; It is successfully created. But when I use call SP_INSERT_DNOTE_HEADER('AB001', NOW(), ''); The server program suddenly dies with error. But when I remove the declare statment, it works smoothly. So I force to use an extra input parameter as variable for my stored procedure. Here is my working code DROP PROCEDURE IF EXISTS SP_INSERT_DNOTE_HEADER; delimiter // CREATE PROCEDURE SP_INSERT_DNOTE_HEADER ( IN in_inv_no VARCHAR(20), IN in_cs_id VARCHAR(10), IN in_inv_date DATE, IN in_inv_remark TEXT ) BEGIN SELECT CONCAT('DNE', LPAD(CONCAT(1 + IFNULL(SUBSTRING(H.INV_NO,4), 0), ''), 10, '0')) INTO in_inv_no FROM TBL_DNOTE_HEADER H ORDER BY H.INV_NO DESC LIMIT 1; IF LENGTH(in_inv_no) <=0 THEN SET in_inv_no = CONCAT('DNE', LPAD('1', 10, '0')); END IF; INSERT INTO TBL_DNOTE_HEADER (INV_NO, CS_ID, INV_DATE, INV_REMARK, DELETED) VALUES (in_inv_no, in_cs_id, in_inv_date, in_inv_remark, 0); END; // delimiter ; call SP_INSERT_DNOTE_HEADER('', 'AB001', NOW(), '') works smoothly this time Does anyone have the same problem that I faced? Any solutions? How to repeat: Having a declare statement in stored procedure