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

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

CREATE TABLE "Customers" (
"CustomerID" NCHAR(5) NOT NULL,
"CompanyName" NVARCHAR(40) NOT NULL,
"ContactName" NVARCHAR(30) NULL,
"ContactTitle" NVARCHAR(30) NULL,
"Address" NVARCHAR(60) NULL,
"City" NVARCHAR(15) NULL,
"Region" NVARCHAR(15) NULL,
"PostalCode" NVARCHAR(10) NULL,
"Country" NVARCHAR(15) NULL,
"Phone" NVARCHAR(24) NULL,
"Fax" NVARCHAR(24) NULL,
PRIMARY KEY ("CustomerID")
);

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

begin

declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @i int = 0
declare @id varchar(max) = ''

while @i < 5
begin
        set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)

    set @i = @i + 1
end

Select (cast(@id as nvarchar(400)))

end

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

begin
declare @randID varchar(5) = ''
declare @selectID varchar(20) = ''
declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @i int = 0
declare @id varchar(10) = ''

while @i < 5
begin
   set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)           
    set @i = @i + 1
end
select @id
set @id = 'ANATR'

SET @selectID = (SELECT CustomerID FROM CUSTOMERS WHERE CustomerID = @id)


while @selectID <> 'NULL'
begin
    set @id = ''
    while @i < 5
        begin
            set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)           
            set @i = @i + 1
        end

    SET @selectID = (SELECT CustomerID FROM CUSTOMERS WHERE CustomerID = @id)   

    SELECT @id
end


end

Here is the insert procedure I have at the moment

CREATE PROCEDURE [dbo].[InsertCustomers]

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30) = NULL,

@ContactTitle nvarchar(30) = NULL,

@Address nvarchar(60) = NULL,

@City nvarchar(15) = NULL,

@Region nvarchar(15) = NULL,

@PostalCode nvarchar(10) = NULL,

@Country nvarchar(15) = NULL,

@Phone nvarchar(24) = NULL,

@Fax nvarchar(24) = NULL

)

AS

SET NOCOUNT OFF;

 INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);

Answer

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

begin

declare @chars char(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare @i int = 0
declare @id varchar(max) = ''


while (1=1)
begin
        set @id = @id + substring(@chars, cast(ceiling(rand() * 26) as int), 1)

    set @i = @i + 1

   IF (NOT EXISTS(SELECT * FROM Customers WHERE CustomerID = @id) AND LEN(@id) = 5)
      BREAK
   ELSE
      CONTINUE
end

Select (cast(@id as nvarchar(400)))

end

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.

Advertisement

Answer