Generate Serial number based on column value in sql server

Question

I have table called cash_voucher it has some columns.

I want to generate voucher number based location id.

I mean generates voucher numbers starting from 1 for each location

For example location id is 1 voucher number will be generate from 1 and also location id is 2 voucher number will generate from 1

below code is working but my doubt is whether its reliable and prevent concurrency problem?

example data to execute stored procedure

exec usp_cv_cash_voucher_create '08-08-2025', 1, 'Test', 5.25, 'Five and Twenty five', 'Test expenses', 'kumar', 1

exec usp_cv_cash_voucher_create '08-09-2025', 2, 'Test', 500, 'five hundred', 'Test expenses - 1', 'john', 2

alter proc usp_cv_cash_voucher_create(
@voucher_date date, 
@head_of_account_id int,
@name varchar(400),
@rupees decimal(10,2),
@rupees_in_words varchar(max),
@towards varchar(max),
@prepared_by varchar(100),
@location_id int
)
as
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @voucher_number INT;
    
    
    BEGIN TRANSACTION;
    
    BEGIN TRY
        -- Get the next voucher number for the specific location
        -- Use table lock hint to prevent concurrent issues
        SELECT @voucher_number = ISNULL(MAX(voucher_number), 0) + 1
        FROM cash_voucher WITH (TABLOCKX)
        WHERE location_id = @location_id;
        
        -- Insert the new cash voucher record
        INSERT INTO cash_voucher (
            voucher_date,
            head_of_account_id,
            name,
            rupees,
            rupees_in_words,
            towards,
            prepared_by,
            location_id,
            voucher_number,
            created_at,
            created_by
        )
        VALUES (
            @voucher_date,
            @head_of_account_id,
            @name,
            @rupees,
            @rupees_in_words,
            @towards,
            @prepared_by,
            @location_id,
            @voucher_number,
            getdate(),
            @prepared_by
        );
        
        -- Return the generated voucher number and ID
        SELECT 
            SCOPE_IDENTITY() as voucher_id,
            @voucher_number as voucher_number,
            'SUCCESS' as status,
            'Cash voucher created successfully' as message;
        
        COMMIT TRANSACTION;
        
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        
        -- Return error information
        SELECT 
            NULL as voucher_id,
            NULL as voucher_number,
            'ERROR' as status,
            ERROR_MESSAGE() as message;
    END CATCH
END;

Table Definition

Answer 1

Incrementing

I would look into sequences instead. This might help. Check if the feature is available on your SQL Server version.

To guarantee consistency, all table updates would need to go through your procedure. External inserts would not provide the desired logic. So you could move the logic to a trigger, but they are easily abused so I do not systematically recommend that. But is seems to me that it could make sense here.

Locking

WITH (TABLOCKX) is supposed to obtain an exclusive lock on the table. But it applies on the select. By the time you proceed to insert, the lock would have been released, and it is conceivable that another process could have altered the table.

Performance

Unless location_id is covered by an index, retrieving the max value involves a full table scan. The more records, the slower this will be. The execution plan can tell you how the query is being resolved and optimized.

Uniqueness

You might then need a unique constraint on both fields location_id and voucher_number to enforce uniqueness. Your table structure is unknown, so this is.

Wrap up

Now disregard all the previous considerations.

This looks like a POS application of some sort. I assume that the sequence number should reflect insertion order, and is nothing more than a regular counter. If you have a timestamp column (created_at), you can already query and display the records in chronological order, filtered by location if desired. The sequence can be generated on the fly in a query, using window functions for example. ROW_NUMBER() could be used for that purpose.

The sequence then exists solely as a virtual/computed column, and is not guaranteed to be constant over time, as you could always insert backdated records. But for accountancy purposes, it's the autoincremented ID that should matter, because it is truly unique and unambiguous. I doubt that the voucher number has a critical function really.