How to rollback stored procedure that updates a table

Why doesn’t this ROLLBACK TRANSACTION statement work?

BEGIN TRANSACTION;
DECLARE @foo INT
EXECUTE [database].[dbo].[get_counter] @CounterID='inventory_records', @nextValue=@foo OUTPUT;
ROLLBACK TRANSACTION;

Background

I’m inserting records into a customer’s ERP system built on SQL Server 19. The ERP database doesn’t have auto-incrementing primary keys. It instead uses a table called counters where each row has a counterID field and an integer value field.

To insert a new row into a table like inventory_record, I first need to call a stored procedure like this:

EXECUTE get_counter @counterID='inventory_record'

This procedure returns an OUT parameter called @nextValue which I then INSERT into the inventory_record table as its uid.

I need to ROLLBACK this stored procedure’s behavior if my insert fails. That way the counter doesn’t increase boundlessly on failed INSERT attempts.

Contents of get_counter stored procedure

It’s dirt simple but also subject to copyright. I’ve summarized and truncated here. The counters are stored as sequences in the DB. So get_counter calls sp_sequence_get_range after checking that the requested counter is legitimate.

ALTER PROCEDURE get_counter
    @strCounterID varchar(64),
    @iIncrementValue integer = 1, 
    @LastValue BIGINT = NULL OUTPUT
AS

SET NOCOUNT ON
BEGIN

DECLARE 
          @nextSeqVar SQL_VARIANT
        , @lastSeqVar SQL_VARIANT

-- code that confirms valid counter name

BEGIN TRY
    -- code that calls [sp_sequence_get_range]
END TRY
BEGIN CATCH
    THROW
END CATCH

RETURN(@LastValue)
END

The Problem

The inventory_record counter always increments. I can’t roll it back.

If I run the SQL at the top of this question from SSMS, then SELECT value FROM counters WHERE counterID = 'inventory_record', the counter increments each time I execute.

I’m new to transaction handling in SQL Server. Any ideas what I’m missing?

Answer

re-post comments as answer for better readability.

The get_counter is using Sequence Numbers (sp_sequence_get_range). Please refer to documentation on Limitation section.

Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back

You may see a simple demo here