Custom Identifier generation in SQL Server Stored Procedure

sql, sql-server, sql-server-2008, stored-procedures

Solution

This approach will be safe under load, e.g. it's not going to return any duplicates, even if lots of client requests come in at the same time (this is "borrowed" from an answer by @remusrusanu to another question on SO).

Basically, you need a sequence table with columns `TenantID`, `TenantPrefix` and `CurrentValue` and then you can use a stored procedure like this to safely fetch 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 point here is: you have to do the incrementing the `IDValue` and the returning of it inside a single `UPDATE` statement. Only with this approach can you be safe under load - all the approaches that have a `SELECT` first, increment, and then `UPDATE` are not safe and can return duplicates.

Update: you cannot just include this snippet of code into a larger procedure of yours! Leave this procedure as is and just call it from your 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

Problem

Currently working on a multi-tenant application, have an issue in generating identifier in a stored procedure. I have a this table which has a meta information about tenant. Tenant ``` TenantId Name IDPrefix, -->Like SFT IDStart --> 000001 ``` Client ``` TenantID ClientIdentfier --> Like SFT000001 ``` In a stored procedure I want to generate the next `ClientIdentifier` like `SFT000002`. How can I do it based on last `ClientIdentifier` value + 1 ? I know only taking last value with this below code. ``` select max(ClientID) + 1 from Client will give 1,2,etc ``` But I think I can't do like ``` DECLARE @CIdentier Varchar(50); select @CIdentier = select max(ClientIdentifier) + 1 from Client to produce 'SFT000002 ``` How can I do like this in a stored procedure? Edit: Tried mark_s answer and it worked like a charm!!!

Original source