Update Primary Table and records having dependencies in SQL Server

Last Reply 4 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.

 

 

You are viewing reply posted by: pandeyism 5 months ago.
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