CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Jayonta Sarkar
Jayonta Sarkar

Reputation: 39

Transaction inside stored procedure with input parameter

ALTER Procedure [dbo].[sp_Update_Quantity]
(
    @Quantity INT,
    @Product_ID INT
)
AS
BEGIN
begin try
Begin Transaction
    update Product
    Set Product.Quantity=(Product.Quantity-@Quantity)
    where (Product.Quantity-@Quantity>0)
    AND @Product_ID IN (Select Product_ID from Product)

    insert into Product_Sales(Product_ID,Quantity)
    values(@Product_ID,@Quantity)
commit Transaction
print 'Successfull Inserted'
end try
begin catch
    Rollback Transaction
    print 'Operation is not Successfull'
end catch
END

My stored procedure is working fine but the problem is it is not checking the 

(quantity - @quantity) > 0

condition.

If my input quantity is greater than the quantity of a specific product data is only inserting into Product_Sales table.But i want if the condition (quantity - @quantity) > 0 fails the transaction will rollback but it is committing the transaction. Why? How to solve the problem?

Upvotes: 1

Views: 941

Answers (1)

M.Ali
M.Ali

Reputation: 69504

ALTER Procedure [dbo].[usp_Update_Quantity]  --<--- See explanation by marc_s in your comments 
(                                                 -- about using sp prefix for your procs
@Quantity INT,
@Product_ID INT
)
AS
BEGIN
begin try
           DECLARE @Stock INT;

           SELECT @Stock =  Product.Quantity 
           FROM Product 
           WHERE Product_ID =  @Product_ID

           IF (@Stock < @Quantity)
            BEGIN
             RAISERROR('Not Enough Stock', 16, 1)
             RETURN
            END

    Begin Transaction
            update Product
            Set Product.Quantity=(Product.Quantity-@Quantity)
            where Product_ID =  @Product_ID


            insert into Product_Sales(Product_ID,Quantity)
            values(@Product_ID,@Quantity)
    commit Transaction
        print 'Successfull Inserted'
end try

begin catch
  IF @@ROWCOUNT > 0
      Rollback Transaction

     print 'Operation is not Successfull'
end catch
END

Upvotes: 1

Related Questions