Geek Answers Handbook

Creating a user identifier in a SQL Server stored procedure

Currently working on a multi-user application, there is a problem of generating an identifier in a stored procedure.

I have this table that contains the meta information about the tenant.

Tenant

TenantId Name IDPrefix, -->Like SFT IDStart --> 000001

Client

 TenantID ClientIdentfier --> Like SFT000001

In the stored procedure, I want to generate the following ClientIdentifier as SFT000002 .

How can I do this based on the last ClientIdentifier value + 1?

I only know the last value with this code below.

 select max(ClientID) + 1 from Client will give 1,2,etc

But I think I can’t do

  DECLARE @CIdentier Varchar(50); select @CIdentier = select max(ClientIdentifier) + 1 from Client to produce 'SFT000002

How can I do this in a stored procedure?

Edit:

Tried the mark_s answer and it worked like a charm !!!

+4
source share
2 answers

This approach will be safe under load, for example. he is not going to return any duplicates, even if many client requests come at the same time (this is “borrowed” from @remusrusanu's answer to another question about SO).

Basically, you need a sequence table with TenantID , TenantPrefix and CurrentValue columns, and then you can use a stored procedure like this to safely retrieve new values:

 -- add a IDValue column to your Tenant table ALTER TABLE dbo.Tenant ADD IDValue INT NOT NULL DEFAULT(0) -- create this procedure to fetch the next value for any given tenant CREATE PROCEDURE dbo.GetNextTenantID @tenantID INT, @NextID VARCHAR(15) OUTPUT AS SET NOCOUNT ON; DECLARE @Out TABLE (NextVal INT, Prefix CHAR(3)) UPDATE dbo.Tenant SET IDValue = IDValue + 1 OUTPUT INSERTED.IDValue, INSERTED.IDPrefix INTO @Out(NextVal, prefix) SELECT TOP 1 @nextID = Prefix + CAST(NextVal AS VARCHAR(10)) FROM @Out GO

The main thing here: you need to increment IDValue and return it inside the single UPDATE . Only with this approach can you be safe under load - all approaches that have SELECT first, increment, and then UPDATE , are not safe and can return duplicates.

Update: cannot include this piece of code in a more complex procedure! Leave this procedure as it is and just call it from the stored procedure - something like:

 ALTER PROCEDURE [dbo].[AddClient] ( @TenantId INT, @FirstName NVARCHAR(100), @LastName NVARCHAR(100), @ContactPerson NVARCHAR(100) ) AS BEGIN SET NOCOUNT ON IF @TenantId IS NULL RAISERROR('The value for @TenantID should not be null', 15, 1) -- with log DECLARE @new_person_id INT DECLARE @new_patient_id INT DECLARE @ClientIdentifier NVARCHAR(50) -- call the stored procedure to get the next ClientIdentifier here EXEC dbo.GetNextTenantID @TenantID, @ClientIdentifier OUTPUT -- then go on and do your other lines of code from here on out ..... ...... END
+5
source

if you have a fixed prefix length and right digit length:

Here is the SQLFiddle demo

 with T as (select (isnull(max(ClientIdentfier),'SFT000000')) maxNum from client ) select substring(maxNum,1,3)+ RIGHT('000000'+ cast(cast(right(maxNum,6) as bigint)+1 as varchar(100)), 6) from T

If you need to get the following number for a specific TenantID, for example TenantID = 2, use the following query:

 with T as (select (isnull(max(ClientIdentfier), select top 1 IDPrefix+IDStart from Tenant where TenantID=2)) maxNum from client where TenantID=2 ) select substring(maxNum,1,3)+ RIGHT('000000'+ cast(cast(right(maxNum,6) as bigint)+1 as varchar(100)), 6) from T
+1
source

More articles:


All Articles