Mysql Stored Function Nested Query (SELECT inside INSERT Query)

Tables

create table category(id int primary key auto_increment, name varchar(30));
insert into category(name) values('Snacks'),('Soft Drink'),('Raw');

create table material(id int primary key auto_increment, name text, catID int references category(id), quantity float, unit text, price float, pur_date date);

create table mStock(name text, catID int, quantity int, unit text);

Stored Function to add Material

CREATE DEFINER=`root`@`localhost` FUNCTION `addMaterial`( nm text, cat text, qty int, un text, pr float) RETURNS int(11)
    DETERMINISTIC
BEGIN

    declare cnt int;

    declare continue handler for 1062
    BEGIN
        return 0;
    END;

    insert into MATERIAL 
            ( name, catID, quantity, unit, price, pur_date) 
     values ( nm, ( select id from CATEGORY where lower(name) = lower(cat) ) , 
              qty, un, pr, curdate() );

    select count(*) into cnt from mSTOCK where lower(name) = lower(nm);

    if( cnt  > 0 )
    then
        update mSTOCK set quantity = quantity + qty where lower(name) = lower(nm);
    else
        insert into mSTOCK values( nm, ( select id from CATEGORY where lower(name) = lower(nm) ), qty, un );
    end if;

    RETURN 1;
END

Checking whether entries are added in table

select * from material;

select * from mStock;

Category ID is added in Material Table but it is not added in mStock Table. I've also tried using select into query but it is not working.

Noticing your where clause in else case:

where lower(name) = lower(nm) 

replace that with

where lower(name) = lower(cat)