Update Primary Table and records having dependencies in SQL Server

Hi nagaraju60,

Refer below sample query.

SQL

CREATE PROCEDURE CheckAndIpdate
	@ItemId INT,
	@OrderId INT
AS
BEGIN
	DECLARE @Id INT
	SELECT @Id = FK_order_Id FROM Tbl_Itms WHERE Item_Id = @ItemId AND FK_order_Id = @OrderId	
	UPDATE Tbl_Itms SET Item_Status = 'C' WHERE Item_Id = @ItemId AND FK_order_Id = @OrderId	
	UPDATE Tbl_Ordrs SET Order_Status = 'C' WHERE Order_Id = @Id
END

Dear @pandeyism,

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

CREATE PROCEDURE CheckAndIpdate
    @ItemId INT,
    @OrderId INT
AS
BEGIN

    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')
    IF(@Item_Cnt=@Item_Stat_Cnt)
    BEGIN
        UPDATE Tbl_Ordrs SET Order_Status='C' WHERE Order_Id = @OrderId
    END	
END