create table specific id in isql

Question

My company generates different readable ids for each table in the database with the format of [prefix]-[incrementing id]

i.e.

Inventory => INV-0001, INV-0002; (INCREMENTAL) Products => PROD-0001, PROD-0002, PROD-0003; (INCREMENTAL)

The problem I am facing is that the ids are generated in c# code and not by the database so it makes it very difficult to insert a new record into the database via Trigger. I'm looking for an approach that would help me to solve this issue.

Answer 1

Encapsulate the ID generation into one or more stored procedures and use them from client as well as from T-SQL side.

-- ======================================================================
-- id table
CREATE TABLE Ids (
   Name VARCHAR(30) PRIMARY KEY CLUSTERED
   ,NextId INT NOT NULL
);
-- ======================================================================
-- init ids for Inventory
INSERT INTO Ids VALUES('Inventory', 1);
GO
-- ======================================================================
-- general procedure to allocate new ids
ALTER PROCEDURE AllocateIds
   @nextId INT OUTPUT
   ,@name VARCHAR(30)
   ,@count INT
AS
SET NOCOUNT ON;

UPDATE Ids SET
   @nextId = NextId
   ,NextId += @count
WHERE Name = @name
GO
-- ======================================================================
-- special procedure to get new inventory ids
ALTER PROCEDURE GetInventoryId
   @inventoryId VARCHAR(30) OUTPUT
AS
SET NOCOUNT ON;

DECLARE @nextId INT;
EXECUTE AllocateIds @nextId OUTPUT, 'Inventory', 1;

SET @inventoryId = 'INV-' + REPLICATE('0', 4 - LEN(CAST(@nextId AS VARCHAR(20)))) + CAST(@nextId AS VARCHAR(20));
GO
-- ======================================================================
-- sample usage
DECLARE @inventoryId VARCHAR(30);
EXECUTE GetInventoryId @inventoryId OUTPUT;
PRINT @inventoryId;

Greets Flo