Mysql Stored Function Nested Query (SELECT inside INSERT Query)

Question

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.

I think you need to research `SELECT LAST_INSERT_ID();` to make this process far more straight forward — RiggsFolly, Oct 08 '19 at 08:49
Why a function? And where do you invoke it? Why not a trigger? — P.Salmon, Oct 08 '19 at 09:03
How do you call the function? Stored functions have additional restrictions over stored procs, so invoking them in the right way is very important. Btw, this should really be implemented as stored proc and not as a function. The return value is only used for error checking purposes, but that's not really what stored functions are there for. — Shadow, Oct 08 '19 at 09:03
I call that function using Java con.prepareCall("{ ? = call addMaterial(?,?,?,?,?)}") — अक्षय परूळेकर, Oct 08 '19 at 09:10

score 1 · Accepted Answer · answered Oct 08 '19 at 09:00

1

Noticing your where clause in else case:

where lower(name) = lower(nm)

replace that with

where lower(name) = lower(cat)

answered Oct 08 '19 at 09:00

Bilal Siddiqui

3,579
1
13
20

Mysql Stored Function Nested Query (SELECT inside INSERT Query)

1 Answers1