Update Primary Table and records having dependencies in SQL Server

Last Reply 5 months ago By nagaraju60

Posted 5 months 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.

 

 

Posted 5 months ago

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

 


Posted 5 months ago

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