Skip to content
Advertisement

t-sql string unique ID (Northwind database)

I’ve been trying to get this right for some time now with no use.

I have a table in mssql database and I want to insert new row using stored procedure

The problem is CustomerID field which contains unique string for each record (ALFKI, BERGS, BERGS, etc.)

I want to make a stored procedure which will insert a row with new data and create an unique CustomerID. Build in functions are out of a question as I need the string to be 5 chars long.

I have a procedure which generates 5 chars ID as follows

And the one that I tried to make work with no use. It is supposed to select an unique id (set @id = ‘ANATR’ is there on purpose to make it go into the loop

Here is the insert procedure I have at the moment

Advertisement

Answer

I believe you can do something like this to make sure you all get a unique id

Set the while condition to be always true and break out of while loop only when both of your requirements are TRUE i.e Length of new ID is 5 and it does not exist in the customers table already.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement