If you only need the voucher code after the voucher is saved then you can use an
identity
column.
You can then access the value assigned as part of the insert using the
scope_identity
function.
DECLARE @VoucherId INT
INSERT INTO dbo.Vouchers (Foo, Bar) VALUES (@Foo, @Bar)
SELECT @VoucherId = SCOPE_IDENTITY()
If you have a requirement to display a voucher code before it's written to the database you can use a counter table.
To ensure there are no clashes between concurrent requests for counter values you perform a variable assignment as part of the update statement which increments the counter.
This process simulates what, as suggested by Tomas, a
sequence [
^]does but has better support for older versions of SQL.
CREATE TABLE Counters
(
CounterType TINYINT,
CounterValue BIGINT
)
DECLARE
@CounterType TINYINT,
@CounterValue BIGINT
UPDATE
dbo.Counters
SET
@CounterValue = CounterValue,
CounterValue = CounterValue + 1
WHERE
CounterType = @CounterType