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;