Single SQL Server query to delete records in two tables

Question

I am creating a stored procedure in SSMS that would delete records in two tables at the same time (my main concern is that one of them will be deleted and if something crashes the other record is left in the database). I will pass the OrderID to my procedure.

I have two tables

Table 1: Orders

OrderID
OrderPrice

Table 2: OrdersLines

OrderID
ProductID

First I created the below procedure ant it looks like it works, but I am afraid that if something crashes I might end up with a deleted OrderLine, but not the order.

CREATE PROCEDURE [dbo].[OrdersDelete] 
    (@OrderId INT)
AS
BEGIN
    DELETE FROM atbv_Sales_OrdersLines 
    WHERE OrderID = @OrderId

    DELETE FROM atbv_Sales_Orders 
    WHERE OrderID = @OrderId
END

So now I have created a new query, that uses inner join to assure that both records are deleted, but it says that the OL and O are invalid objects. Code below:

CREATE PROCEDURE [dbo].[OrdersDelete] 
    (@OrderId INT)
AS
BEGIN
    DELETE OL, O 
    FROM atbv_Sales_OrdersLines 
    INNER JOIN atbv_Sales_Orders
    WHERE OL.OrderID = O.OrderID AND OL.OrderID = @OrderId
END

I would like to hear your solutions. Maybe I was right the first time, but can someone explain this to me?

Answer 1

Your first solution is perfectly acceptable with transaction:

CREATE Procedure [dbo].[OrdersDelete] 
(@OrderId int)
AS
BEGIN
BEGIN TRAN
    DELETE FROM atbv_Sales_OrdersLines WHERE OrderID = @OrderId
    DELETE FROM atbv_Sales_Orders WHERE OrderID = @OrderId
COMMIT -- if we didn't encounter any errors - commit transaction. otherwise it will be rolled back automagically
END