0

I am learning more about SQL as I work on this Database project. Today I encountered Transactions, and after looking at what they are and how they work, my first thought was, "Shit, I need to change my Procedures to use these."

Throughout today I have been looking on here, and Google is trying to find out when it is appropriate to use a Transaction, on how to correctly write the syntax for one. I honestly haven't found much, so I'm essentially writing this so that I can see if I am implementing these properly, and for anyone who may be looking for something like this in the future (hello Google).

So I've mostly been throwing Transactions into any Procedure I have created which either inserts data or deletes data. It seems to me that if I am querying data, not manipulating it, transactions aren't necessary. If I am wrong in this thinking, please feel free to illuminate me.

So this is an example of me using a Transaction in a Procedure that is Deleting Data:

CREATE PROCEDURE DeleteOrder
    @OrderNum VARCHAR(20)
) AS
BEGIN
    DECLARE @OrderID INT

    IF(EXISTS(SELECT order_id FROM purchase_orders WHERE order_number = @OrderNum))
    BEGIN
        SELECT @OrderID = order_id
          FROM purchase_orders
         WHERE order_number = @OrderNum
    END
    ELSE
        RETURN 1

    BEGIN TRANSACTION DelOrder
        BEGIN TRY
            DELETE FROM order_parts
             WHERE order_id = @OrderID
            DELETE FROM purchase_price_logs
             WHERE order_id = @OrderID
            DELETE FROM purchase_orders
             WHERE order_id = @OrderID
            COMMIT TRANSACTION DelOrder
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION DelOrder
            RETURN 1
        END CATCH
END

The first question I have is; Is my syntax correct? Am I implementing the Transaction appropriately? Is there a way you would code what I am trying to do differently? For that last question, it's more about the whole Procedure than just the Transaction segment.

Finally, I have a question I thought of while writing this post. Is it possible to have said a 'Master Rollback' or 'Master Commit' procedure, where while you have a connection going on with the database, no transactions are committed until your 'Master Commit' Procedure is called? Something like.

MasterCommit
    For Each Transaction
        Commit Transaction

With something similar being the same for Rolling back. I know of the existence of SavePoints, so is this where they'd be helpful? More so with the Rollbacks than the Commits I suppose.

  • 1
    These are a lot of very different questions. Transaction looks fine, you might want to raise an error inside CATCH so the consumer knows it failed (and why, since there are multiple possible reasons), unless you are trying to make it fail gracefully. – Aaron Bertrand Aug 1 '18 at 17:49
  • 1
    As for IF EXISTS (SELECT) SELECT @var = val from same SELECT just do SELECT @var = ; IF @var IS NULL RETURN; - there is no reason to query the table once just to query it a second time. – Aaron Bertrand Aug 1 '18 at 17:50
  • 1
    And as for your master commit idea, there is not really any concept of multiple concurrent transactions by the same session. Yes, you can increase trancount > 1, but one commit commits them all, so the master doesn't really serve any purpose. – Aaron Bertrand Aug 1 '18 at 17:51
  • 1
    Sorry, I have never used VB.Net, but I know in C# you have access to the exceptions raised by THROW() or RAISERROR(). I don't know what you can do with PRINT output. – Aaron Bertrand Aug 1 '18 at 17:53
  • 1
    VB.Net works similarly. – Jacob H Aug 1 '18 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.