11

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest this transaction in a stored procedure I run into lots of syntax errors.

How can I encapsulate the following code so it can be easily called?

BEGIN TRANSACTION AssignUserToTicket
GO

DECLARE @updateAuthor varchar(100)
DECLARE @assignedUser varchar(100)
DECLARE @ticketID bigint

SET @updateAuthor = 'user1'
SET @assignedUser = 'user2'
SET @ticketID = 123456

    UPDATE tblTicket SET ticketAssignedUserSamAccountName = @assignedUser WHERE (ticketID = @ticketID);
    INSERT INTO [dbo].[tblTicketUpdate]
           ([ticketID]
           ,[updateDetail]
           ,[updateDateTime]
           ,[userSamAccountName]
           ,[activity])
     VALUES
           (@ticketID,
           'Assigned ticket to ' + @assignedUser,
           GetDate(),
           @updateAuthor,
           'Assign');
GO
COMMIT TRANSACTION AssignUserToTicket
  • 1
    It would likely help if you added details into your question about what exactly the "errors" are (use the edit link underneath your question). Also, please take the tour. Thanks! – Max Vernon Apr 1 '16 at 20:56
14

You like need to wrap that code in CREATE PROCEDURE ... syntax, and remove the GO statements after BEGIN TRANSACTION and before COMMIT TRANSACTION.

GO
CREATE PROCEDURE dbo.AssignUserToTicket
(
     @updateAuthor varchar(100)
    , @assignedUser varchar(100)
    , @ticketID bigint
)
AS
BEGIN
    BEGIN TRANSACTION;
    SAVE TRANSACTION MySavePoint;
    SET @updateAuthor = 'user1';
    SET @assignedUser = 'user2';
    SET @ticketID = 123456;

    BEGIN TRY
        UPDATE dbo.tblTicket 
        SET ticketAssignedUserSamAccountName = @assignedUser 
        WHERE (ticketID = @ticketID);

        INSERT INTO [dbo].[tblTicketUpdate]
            (
            [ticketID]
            ,[updateDetail]
            ,[updateDateTime]
            ,[userSamAccountName]
            ,[activity]
            )
        VALUES (
            @ticketID
            , 'Assigned ticket to ' + @assignedUser
            , GetDate()
            , @updateAuthor
            , 'Assign'
            );
        COMMIT TRANSACTION 
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION MySavePoint; -- rollback to MySavePoint
        END
    END CATCH
END;
GO

Also note, I have added a TRY...CATCH statement block to allow performing a ROLLBACK TRANSACTION statement in case some error occurs. You probably need better error handling than that, but without knowledge of your requirements, that is difficult at best.

Some good reading:

  1. Always specify the schema

  2. Stored Procedure Best Practices

  3. Bad habits to avoid

  • 1
    You still want to have a saved transaction. If you put a transaction in a SP and the SP gets wrapped in another transaction stuff is going to fail. sqlstudies.com/2014/01/06/… – Kenneth Fisher Apr 1 '16 at 21:30
  • thanks for pointing me at that. I know there are no nested transactions in SQL Server, however I wasn't aware of the SAVE TRANS command's implications. – Max Vernon Apr 1 '16 at 21:35
7

If you want to properly handle nested Stored Procedures that can handle Transactions (whether started from T-SQL or app code) then you should follow the template that I described in the following answer:

Are we required to handle Transaction in C# Code as well as in stored procedure

You will notice two differences there from what you are attempting here:

  1. The use of RAISERROR within the CATCH block. This bubbles the error up to the calling level (whether in the DB or app layer) so a decision can be made regarding the fact that an error occurred.

  2. No SAVE TRANSACTION. I have never found a case for using this. I know some folks prefer it, but in everything I have ever done at any place that I have worked, the notion of an error occurring within any of the nested levels implied that whatever work was already done was invalid. By using SAVE TRANSACTION you are only reverting back to the state just prior this Stored Procedure being called, leaving the existing process as otherwise valid.

    If you want more details on SAVE TRANSACTION, then please take a look at the information in this answer:

    How to rollback when 3 stored procedures are started from one stored procedure

    Another issue with SAVE TRANSACTION is a nuance of its behavior, as noted in the MSDN page for SAVE TRANSACTION (emphasis added):

    Duplicate savepoint names are allowed in a transaction, but a ROLLBACK TRANSACTION statement that specifies the savepoint name will only roll the transaction back to the most recent SAVE TRANSACTION using that name.

    Meaning, you need to be very careful to give each Save Point in each Stored Procedure a name that is unique across all Save Points in all Stored Procedures. The following examples illustrate this point.

    This first example shows what happens when you reuse the Save Point name; only the lowest-level Save Point is rolled-back.

    IF (OBJECT_ID(N'tempdb..#SaveTranTestA') IS NOT NULL)
    BEGIN
        DROP TABLE #SaveTranTestA;
    END;
    CREATE TABLE #SaveTranTestA (SomeVal INT NOT NULL);
    
    BEGIN TRAN; -- start level 1
    SAVE TRANSACTION MySavePoint;
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 1
    
    INSERT INTO #SaveTranTestA (SomeVal) VALUES (100);
    
    BEGIN TRAN; -- start level 2
    SAVE TRANSACTION MySavePoint;
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 2
    
    INSERT INTO #SaveTranTestA (SomeVal) VALUES (200);
    
    COMMIT; -- exit level 2
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 1
    SELECT * FROM #SaveTranTestA;
    -- 100
    -- 200
    
    ROLLBACK TRANSACTION MySavePoint; -- error occurred; undo actions up to this point
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 1
    SELECT * FROM #SaveTranTestA;
    -- 100
    
    COMMIT; -- exit level 1
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 0
    SELECT * FROM #SaveTranTestA;
    -- 100
    

    This second example shows what happens when you use unique Save Point names; the desired level's Save Point is rolled-back.

    IF (OBJECT_ID(N'tempdb..#SaveTranTestB') IS NOT NULL)
    BEGIN
        DROP TABLE #SaveTranTestB;
    END;
    CREATE TABLE #SaveTranTestB (SomeVal INT NOT NULL);
    
    BEGIN TRAN; -- start level 1
    SAVE TRANSACTION MySavePointUno;
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 1
    
    INSERT INTO #SaveTranTestB (SomeVal) VALUES (100);
    
    BEGIN TRAN; -- start level 2
    SAVE TRANSACTION MySavePointDos;
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 2
    
    INSERT INTO #SaveTranTestB (SomeVal) VALUES (200);
    
    COMMIT; -- exit level 2
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 1
    SELECT * FROM #SaveTranTestB;
    -- 100
    -- 200
    
    ROLLBACK TRANSACTION MySavePointUno; --error occurred; undo actions up to this point
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 1
    SELECT * FROM #SaveTranTestB;
    -- <no rows>
    
    COMMIT; -- exit level 1
    
    SELECT @@TRANCOUNT AS [TranCount]; -- 0
    SELECT * FROM #SaveTranTestB;
    -- <no rows>
    
  • That's why I use @@NESTLEVEL to fabricate my SAVE TRANSACTION savepoint name. – Vincent Vancalbergh Apr 27 '18 at 9:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.