CODEWITHSUNDEEP
sqlsql-server
oledu.com
oledu.com

Reputation: 339

MSSQL Try Catch Rollback and Conditional RETURN

SET XACT_ABORT ON

BEGIN TRY

    BEGIN TRANSACTION

    UPDATE [Members] SET [Count] = [Count] - 1 WHERE [Count] > 0; 
    **return 0 here if this statement updated 0 rows**

    INSERT INTO [Sessions] ([Name], [Etc]) VALUES ('Test', 'Other')

    COMMIT TRANSACTION

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

    RETURN 0

END CATCH

SELECT SCOPE_IDENTITY(); **only return the identity if the first statement updated 1 row and no exceptions occurred.**

With the code above, is there a way to return 0 if the first UPDATE updated no rows? I only want the INDENTITY() if the first UPDATE edits a row and no errors occur. Is this possible?

Upvotes: 0

Views: 634

Answers (2)

gbn
gbn

Reputation: 432220

Only read SCOPE_IDENTITY if you actually INSERT This way, you have one exit point only

BEGIN TRY

    BEGIN TRANSACTION

    UPDATE [Members] SET [Count] = [Count] - 1 WHERE [Count] > 0; 
    IF @@ROWCOUNT = 1
    BEGIN
        INSERT INTO [Sessions] ([Name], [Etc]) VALUES ('Test', 'Other')
        SET @rtn = SCOPE_IDENTITY();
    END
        SET @rtn = 0;

    COMMIT TRANSACTION;
    SELECT @rtn
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can use @@ROWCOUNT to get the number changed. This should work:

UPDATE [Members] SET [Count] = [Count] - 1 WHERE [Count] > 0; 
IF @@ROWCOUNT = 0
BEGIN
    COMMIT TRANSACTION;
    RETURN;
END;

Because no rows are updated, I suppose rolling back the transaction would be equivalent.

To get the identify values inserted, I would recommend that you learn to use the OUTPUT clause in INSERT (documented here). This is a good habit to get into, although the syntax is a bit more cumbersome (you have to define a table/table variable to store the inserted values). It has no race conditions and it allows you return multiple identity values.

Upvotes: 2

Related Questions