CODEWITHSUNDEEP
c#sql-serveruniqueincrement
Nixfee
Nixfee

Reputation: 5

Generate a unique reference number with todays date

I am using #C and SQL.

I'm looking to generate a unique reference number like XN-ddMMyyyy-0001

If SQL has XN-26112020-0001 then create XN-26112020-0002 and so on....

How would I go about doing this using SQL database and C#?

I'm guessing it'll require SQL IF NOT EXIST (SELECT) INSERT INTO statement.

Upvotes: 0

Views: 639

Answers (2)

Dale K
Dale K

Reputation: 27226

Its a lot of work to generate sequential values for each combination of XN-ddMMyyyy - it is much simpler if the unique reference is truly unique. You can do this by storing the first part of the reference and using and identity column for the unique part and then combining them in a computed column. Of course if you are generating a lot of records then you'll need more than 4 digits.

create table MyTable (
    -- ... other columns
    BaseReference varchar(12)
    , uniqueReference int identity (1,1)
    , FullReference as BaseReference + '-' + convert(varchar(4),uniqueReference)
);

If you truly have to make it unique only for the base reference then you need to do something like the following which requires an exclusive table lock to avoid the possibility of 2 processes adding a duplicate reference at the same time:

declare @BaseReference varchar(12) = 'XN-26112020-', @UniqueReference int;

begin tran;

-- Lock the table until the transaction is complete to ensure no duplicates
-- Get the current max value of the unique portion
select @UniqueReference = max(try_convert(int,substring(Reference, 13, 4)))
from dbo.MyTable with (tablockx)
where substring(Reference, 1, 12) = @BaseReference;

set @UniqueReference = coalesce(@UniqueReference,0) + 1;

insert into dbo.MyTable (/*Other Columns*/ Reference)
  values (/*Other Columns*/ @BaseReference + format(@UniqueReference,'0000'))

commit;

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

one way is to use SEQUENCE like below:

CREATE SEQUENCE seqrefno
AS INT 
START WITH 1
INCREMENT BY 1 
GO


SELECT 'NX-'+CONVERT( CHAR(6),GETDATE() ,12) +'-'+  CAST(NEXT VALUE FOR seqrefno AS VARCHAR(10))

Upvotes: 1

Related Questions