Creating a Unique Identifier Using SQL

Question

Is there some kind of SQL coding that can generate a unique identifier when the default get button is clicked? I am looking for a system for a set of physical paper files before they are put into storage. The problem is that there are 3 offices, and each office needs a sequential number system (i.e. P001, P002, P003 and C001, C002 ...).

Below is the code that I still created a prefix to a unique identifier 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 is that the code can generate the entire file number, for example. P110001, P110002 (where P, C or denotes the office where the file is located, and 11 denotes the year the file was placed in storage)

any pointers greatly appreciated

Answer 1

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

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

Answer 2

I think that there could be a misunderstanding caused by the original wording of the question.

Now I assume that you need a process in which every office should be able to generate a new guaranteed serial number with the click of a button each time it processes a file?

Then, gaps are executed in sequence to examine potential missing files. It is right?

If so, you can use something like this to generate 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

Answer 3

row_number . , id row_number.

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