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.

Try the ifnull function to protect against unwanted NULL values.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.