Question 60868 in Mysql Question posted in
The official MySQL documentation can be found here.

insert value through procedure in a table by mysql

i want to insert values in a table by using procedure
i have 2 tables sales and products in a product table i have id , product_name,product_price,
product_quantity and in sales table i have p_id and p_qty
by using procedure when a user add values in a sales table procedure take
2 arguements 1 for p_id and other for p_qty
if p_qty is less than 0 or greater than product_quantity than showstock not available

i want answer if above statement
by using procedure when a user add values in a sales table procedure take
2 arguements 1 for p_id and other for p_qty
if p_qty is less than 0 or greater than product_quantity than showstock not available

enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    here is my query

    DELIMITER //
CREATE PROCEDURE find_qty_(
    product_id_proc INT,
    product_qty_proc INT
)
BEGIN
    DECLARE
        remaining_stock INT; 
        SELECT product_quantity INTO remaining_stock FROM products
        WHERE id = product_id_proc; 
        IF product_qty_proc>=0 OR product_qty_proc>remaining_stock THEN 
            BEGIN SELECT "stock not available";
            END;
        ELSE 
            INSERT into sales(p_id,p_qty)VALUES(product_id_proc,product_qty_proc);
        END IF; 
    END;;

  2. 0

    product_qty_proc > products.product_quantity needs to refer to a specific row in the products table in a query. You can use a subquery to select the quantity for product_id_proc. Replace that condition with:

    product_qty_proc > (
    SELECT product_quantity 
    FROM products 
    WHERE product_id = product_id_proc
)

    Also, product_qty_proc>0 should be product_qty_proc<0.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search