Stored procedure with if statement not working

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

Answer

There is a conflict between singular value of the @quantity parameter and multiple rows from the SELECT statement. So it prevents the comparison between them.

Please try to change the if clause logic as follows:

IF EXISTS (SELECT purchase_quantity 
        from purchase_info  
        WHERE product_id = @product_id and color = @color
            AND purchase_quantity <= @quantity)