Update Primary Table and records having dependencies in SQL Server

Last Reply one month ago By nagaraju60

Posted one month 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 one month 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 one month 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