vendredi 14 mai 2021

Stored procedure with if statement not working in SQL Server

I want to create a stored procedure to check if the product has enough quantity in the purchase_info table.

This is my code. But it is not working, the result doesn't show me the if...else query.

Can anyone help me fix it? Thank you.

CREATE PROCEDURE dbo.usp_CheckProductStock
    (@product_id varchar(50),
     @color varchar(80),
     @quantity int)
AS
BEGIN;
    IF @quantity <= (SELECT purchase_quantity 
                     FROM purchase_info  
                     WHERE product_id = @product_id AND color = @color) 
    BEGIN
        PRINT 'There is enough stock'
    END
    ELSE
    BEGIN
        SELECT product_id, color, purchase_quantity 
        FROM purchase_info 
        WHERE product_id = @product_id AND color = @color;
    END
END;


EXEC dbo.usp_CheckProductStock @product_id='LF10001', @color='APR', @quantity=15

Aucun commentaire:

Enregistrer un commentaire