Avoid duplicate numbers

[Raul2005]

This store procedure generate ramdom numbers
but there is a slight probability that it could generates duplicate numbers. Can some one help me to modified it so at the time it is inserted in a table field it could verify if the same number exist.In case it exist generate another number verify for duplicates and inserted in case there is not duplicates.
Thanks


alter procedure Random(
@top int
) as begin

declare @lyear int,
@month int,
@day int,
@hour int,
@minute int,
@second int,
@millisecond int,
@curdate datetime

select @curdate = getdate()

select @lyear = datepart(year, @curdate),
@month = datepart(month, @curdate),
@day = datepart(day, @curdate),
@hour = datepart(hour, @curdate),
@minute = datepart(minute, @curdate),
@second = datepart(second, @curdate),
@millisecond = datepart(millisecond, @curdate)

return (((((( @lyear + @month * @hour ) * @hour ) % 10000 ) * ( @minute * @second + @millisecond % 1000 ) ) % 10000 ) % @top )

end

 

[gmmastros]

Have you looked at this faq's? faq183-1142

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hwkranger]

omg, i'm very afraid of this sp.

My friend, you need to use unique distictive identifiers of your data as a primary key for your data.

To solve your problem though,

declare @lyear int,
        @month int,
        @day int, 
        @hour int,
        @minute int,
        @second int,
        @millisecond int,
        @curdate datetime,
        @data bigint
set @data = 0
while exists(Select * from your table where id = set @data) or @data = 0
BEGIN
   select @curdate = getdate()

   select @lyear = datepart(year, @curdate),
          @month = datepart(month, @curdate),
          @day = datepart(day, @curdate),
          @hour = datepart(hour, @curdate),
          @minute = datepart(minute, @curdate),
          @second = datepart(second, @curdate),
          @millisecond = datepart(millisecond, @curdate)

   set @data = (((((( @lyear + @month * @hour ) * @hour ) % 10000 ) * ( @minute * @second + @millisecond % 1000  ) ) % 10000 ) % @top )
END

return @data

Randall Vollen
National City Bank Corp.