[2005] Generating a Unique Code of 6 Character

[killer7k]

Hello ,

after the validation of the fields , i need to insert i a field a unique code of 6 Character in a table , every time i will get a new Code when inserting a new line in a table
any one has an idea ?


Thanks !

[Tom Sawyer]

Since you're using it for an update, the easiest way would be to do the whole thing in a couple of stored procedures. Create a stored procedure that generates a random string of six characters and call that from another one that checks to see if it's unique in your table. If it's not unique, run the loop again; if it is, update the table.

create procedure sp_generaterandom
@word varchar(6) output
as

declare @i int, @Random int, @Upper int, @Lower int

SELECT @Lower = 65,
	@Upper = 90,
	@word = '',
	@i = 1

while @i <= 6
begin

	SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

	SELECT @word = @word + CHAR(@Random)

	SELECT @i = @i + 1

end

go

Then in your insert stored procedure

declare @word varchar(6), @loop int

select @loop = 0

while @loop = 0
begin

    exec sp_generaterandom @word out

    if not exists (select 'x' from <table> where <field> = @word)
    begin
        select @loop = 1
    end

end

//Insert data here using @word

[mendhak]

Hello ,

after the validation of the fields , i need to insert i a field a unique code of 6 Character in a table , every time i will get a new Code when inserting a new line in a table
any one has an idea ?


Thanks !

Sounds like a primary key to me. Why not set your table's Identity Seed to be 100000 and increment by 1?

And to 'get' this when inserting a new row, use SCOPE_IDENTITY()

[killer7k]

@Tom Sawyer ,

Much Appreciated Your Help but too Advanced ! & i need it from Asp.Net !
Thanks !

@mendhak ,
nop i wish if it was a primary key , the table has its own Primary Key

i'am searching something like generation a random of 6 character
& then save it & compare it with the one of the table
if not exists & then insert it

havent succeed on it

Thanks !

[mendhak]

And if it does exist? Generate again? Will that not be inefficient, these multiple calls? Also, what pattern do you want to follow for these random strings? What if you just use rand.Next(100000,999999)? What's wrong with doing it in the stored procedure as shown in Post #2? You should explain your context for usage a little better. There may be a better way to do this.

[Tom Sawyer]

@Tom Sawyer ,

Much Appreciated Your Help but too Advanced ! & i need it from Asp.Net !
Thanks !

The same code logic would work in .NET. The only difference is that you'd need to go and query the database in order to see if it's unique and that's an extra roundtrip that's not needed.

Also, I'm of the opinion that you should never be doing any inserts, updates or deletes outside of stored procedures, since giving permissions for that to a login (which is what's needed for dynamic ones to work) means you essentially have zero security on your database.

[killer7k]

Thanks You Guy ,
i think the situation is simple , i just havent clear it very well
oK !
a i have table Player(PlayerId,PlayerName,PlayerS,KeyG)
the last field KeyG to be Generated & it will be unique

what i have suggest & its the simple way for me

(cmd : sqlcommand)
(dr :sqldatareader)
(con : sqlconnection)

dim rand as Random
dim KeG as String
KeG=rand.next(100000,999999)
cmd=con.createCommand
cmd.commandtext="select KeyG from Player where KeyG='" & KeG & "'"
con.open()
dr=cmd.executereader()
if dr.read=true then
//The Full Query to be inserted in the Table


what do You Think ?

Thanks

[mendhak]

What'll you do if it does exist?

[killer7k]

oops !

i wrote quickly , i have to regenerate the random
else
then insert !!