locked
product_id cannot be null RRS feed

  • Question

  • User1997074450 posted
    I have this table:
    
    | Field             | Type        | Null | Key | Default | Extra |
    +-------------------+-------------+------+-----+---------+-------+
    | product_id        | smallint(6) | NO   | PRI | NULL    |       |
    | product_name      | varchar(40) | NO   |     | NULL    |       |
    | supplier_id       | smallint(6) | YES  | MUL | NULL    |       |
    | category_id       | smallint(6) | YES  | MUL | NULL    |       |
    | quantity_per_unit | varchar(20) | YES  |     | NULL    |       |
    | unit_price        | double      | YES  |     | NULL    |       |
    | units_in_stock    | smallint(6) | YES  |     | NULL    |       |
    | units_on_order    | smallint(6) | YES  |     | NULL    |       |
    | reorder_level     | smallint(6) | YES  |     | NULL    |       |
    | discontinued      | int(11)     | NO   |     | NULL    |       |
    +-------------------+-------------+------+-----+---------+-------+
    
    I have this procedure:

    <div>

    Create procedure productsadd (IN p_productid smallint(6), IN p_product_name varchar(40),  IN p_supplierid smallint(6), IN p_categoryid smallint(6),  IN p_quantityperunit varchar(20), IN p_unitprice double, IN p_unitsinstock smallint(6), IN p_unitsonorder smallint(6), IN p_reorderlevel smallint(6), IN  p_discontinued int(11))
    INSERT INTO products( product_id, product_name, supplier_id, category_id, quantity_per_unit, unit_price, units_in_stock, units_on_order, reorder_level, discontinued )  VALUES ( @p_productid,  @p_name, @p_supplierid, @p_categoryid, @p_quantityperunit, @p_unitprice, @p_unitsinstock, @p_unitsonorder, @p_reorderlevel, @p_discontinued )

    </div> <div></div>

    
    
    I am passing these values 
    
    call productsadd(80,'Vinay4',1,1,'12 boxes',14,32,0,15,0);

    If I use the insert statement directly it works.

    Thursday, October 22, 2020 2:45 AM

Answers

  • User1997074450 posted

    I updated the procedure to the one listed below and it works now.


    DELIMITER // create procedure ProductsAdd(IN p_product_id smallint(6), IN p_product_name varchar(40), IN p_supplierid smallint(6), IN p_categoryid smallint(6), IN p_quantityperunit varchar(20), IN p_unitprice double, IN p_unitsinstock smallint(6), IN p_unitsonorder smallint(6), IN p_reorderlevel smallint(6), IN p_discontinued int(11)) BEGIN insert into products ( product_id, product_name, supplier_id, category_id, quantity_per_unit, unit_price, units_in_stock, units_on_order, reorder_level, discontinued) values (p_product_id, p_product_name, p_supplierid, p_categoryid, p_quantityperunit, p_unitprice, p_unitsinstock, p_unitsonorder, p_reorderlevel, p_discontinued); END // DELIMITER ;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 23, 2020 6:25 PM