Remove From My Forums

product_id cannot be null

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

Answered by:

product_id cannot be null

Question

Answers