DELIMITER $$
DROP PROCEDURE IF EXISTS `AddTranMast` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddTranMast`(mDocDt date,mDocType CHAR(2))
begin
declare mDocNo char(5);
declare Code double;
select max(docno) into Code from tranmast;
if code=00000 then
set mDocNo=00001;
else
set Code=(100000+Code+1);
set mDocNo=substring(Code,2,5);
end if;
insert into tranmast(docno,docdt,doctype) values(mDocNo,mDocDt,mDocType);
end $$
DELIMITER ;
I want to check whether the Max(Code) is NULL for blank database [if code=00000 then].
But It is generating Error : mDocNo can't be NULL. as I think the max(docno) is generating NULL value as the Database is blank now. Please help me. Suggest me a way to check the NULL value before it give an ERROR message.