CODEWITHSUNDEEP
sqlsql-server
James Parish
James Parish

Reputation: 337

Create a unique identifier using SQL

Is there some SQL coding which can generate a unique identifier on the click of a get default button? I'm looking for a system to number physical paper files before they are placed into storage. Problem is there are 3 offices, and each office needs a sequential number system (i.e. P001, P002, P003 and C001, C002...).

Below is the code i have so far to generate the prefix to the unique id number.

SELECT CASE WHEN ptBranch=3 THEN 'P' WHEN ptBranch=4 THEN 'A' ELSE 'C' END + CONVERT(VARCHAR(2),GETDATE(),12) FROM LAMatter WHERE ptMatter = $Matter$

The idea will be that the code could generate the whole file number e.g. P110001, P110002 (where P, C or A denotes the office the file is in, and 11 denotes the year the file was placed into storage)

any pointers greatly appreciated

Upvotes: 3

Views: 8769

Answers (3)

DForck42
DForck42

Reputation: 20357

I'd use the row_number function with a creation date. if you're batch inserting then you'll also want to order by the id column in the row_number function.

declare @records table (id int identity(1,1), CreationDate datetime, Name varchar(50), Section char(1), FileID varchar(10))

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:57:49', 'abc','p'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:57:50', 'def','p'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:00', 'ghi','c'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:50', 'jkl','d'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:51', 'mno','c'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:52', 'pqr','p'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:53', 'def','p'

update @records
set FileID=a.FileID
from 
(
    select id,
    Section + cast(row_number() over (partition by Section order by CreationDate, Section) as varchar(10)) as FileID
    from @records
) a
    inner join @records b
        on a.id=b.id

select * from @records

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453277

I think there might have been a misunderstanding caused by the initial phrasing of the question.

I'm now envisaging that you need a process whereby each office needs to be able to generate a new guaranteed sequential number at a click of a button every time they process a file?

Any gaps in the sequence are then followed up to investigate potential missing files. Is that correct?

If so you can use something like this to generate the numbers.

CREATE TABLE Sequences
(
OfficeCode char(1) primary key,
SequenceNumber int NOT NULL DEFAULT (0)
)

INSERT INTO Sequences(OfficeCode)
SELECT 'P' UNION ALL SELECT 'C' UNION ALL SELECT 'A'

GO

CREATE PROC dbo.GetSequence
@OfficeCode char(1),
@val AS int OUTPUT,
@n as int =1
AS
UPDATE dbo.Sequences 
SET @val = SequenceNumber = SequenceNumber + @n
WHERE OfficeCode = @OfficeCode;

SET @val = @val - @n + 1; 

GO


DECLARE @val  int

EXEC dbo.GetSequence 'C', @val output
select @val

Upvotes: 0

Dan Grossman
Dan Grossman

Reputation: 52372

The SQL Server function newid() will generate a GUID.

Your SQL queries do not generate buttons or anything else. SQL is a language for querying databases, not for writing software interfaces.

Upvotes: 11

Related Questions