Update Primary Table and records having dependencies in SQL Server

Last Reply one year ago By nagaraju60

Posted one year ago

Hai all,

      I Have a tables like below.

Table Name : Tbl_Ordrs

Order_Id Order_Num Order_Status
1 AND/123 E
2 CBF/124 E
3 DVB/168 E

here Order_Id is Primary_Key

Table Name : Tbl_Itms

Item_Id FK_order_Id Item_Qty Item_Status
1 1 3 E
2 1 7 E
3 3 9 E

Here Item_Id is Primary_Key

From Front End, I have to pass parameters Order_Id, Item_Id.

I want to execute the below conditions.

1: by Using Item_Id we have to update table "Tbl_Itms" Item_Status as 'C'
2: After updating "Tbl_Itms" we have to check , if the Item_Status all are 'C' or not using "FK_order_Id", if all status are 'C' then Update
Tbl_Ordrs Order_Status='C' based on Order_Id. if it all status are not 'C' then no need to update Tbl_Ordrs status.

The above conditions all are execute in single stored procedure. so, how to achieve all these conditions.



You are viewing reply posted by: nagaraju60 one year ago.
Posted one year ago

Dear @pandeyism,

Thanks for your valuable reply. I am getting my output by using below Stored Procedure.

    @ItemId INT,
    @OrderId INT

    Declare @Item_Stat_Cnt int			
    Declare @Item_Cnt int
    UPDATE Tbl_Itms SET Item_Status='C' WHERE Item_Id=@ItemId AND FK_order_Id=@OrderId 	
    SET @Item_Cnt=(SELECT Count(*) as Itm_Cnt FROM Tbl_Itms WHERE Item_Id=@ItemId AND FK_order_Id=@OrderId)
    SET @Item_Stat_Cnt=(SELECT Count(*) as Item_Stat_Cnt FROM Tbl_Itms WHERE FK_order_Id=@OrderId AND Item_Status='C')
        UPDATE Tbl_Ordrs SET Order_Status='C' WHERE Order_Id = @OrderId