Insert Randon Number

[amorous]

Hi all,

I am using SQL server 2000 and ASP. I want to insert random number into of the database fields and not sure of how to do.

Presently i have set field "RandNum" as 'nvarchar' type. I want my table to look like the following.

EmpName|ReqID|desc|RandNum
__________________________
John|191|Printer Jammed|123SFSW

The first 3 fields are entered by the user in one of the forms and i want to populate the fourth field with a random.

Should i do this in ASP programming code or in the database??

Any Suggestions.

Thanks in advance.

 

[eramgarden]

You can use "Identity" field and have SQL create the numbers for you.

in EM, right click on the table, choose design. Make that col: int, then at the bottom, where it says "identity", "No", make that Yes. You can even choose the increment.

But with identity, you cant have the alpha numeric.

 

[SQLSister]

NO identity will give you unique values but the wont be random. You might look at the unique identifier data type.

 

[mwa]

I know in sql there is a random function. I've never had to use it (and not sure if I'm using it correctly) but here is something quick that will generate a random number from 0 to 1000 (or adjust the number as needed):

select cast((rand()*1000) as int)

mwa
<><

 

[royjimenez]

create 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


The life is too short to cry and long enough to try it... God bless us.

 

[amorous]

Thanks for your suggestions. I think i found a solution to my problem

VJ

 

[mwa]

royjimenez - I'm not tyring to be argumentative here, but, is your sp truly random?

Yes, it generates a different number for each millisecond of every day. But, theoretically, 2 transactions could generate the same number. If I entered a specific date & time into your function (instead of the getdate), I would get the same number every time I executed the procedure. This is not random. A random function would return a different unpredictable number every time.

mwa
<><

 

[royjimenez]

i agree with u... i made it and i didn't make a test..

u could keep a value and generate a new value ...

examplo:

I keep 5

f( 5 ) generates -> 300
now, i keep 300

f ( 300 ) generates -> 157
now, i keep 157




it's only a idea

Regards

The life is too short to cry and long enough to try it... God bless us.

 

[UrlOnz]

True randomness is hard to come by. In C and VB both the random numbers are seeded by the clock if you use the randomize function with random. You would have to have a hellish lot of transactions to hit two at once in the same millisecond
About all you ever accomplish is the appearance of randomness. My programming instructor and I wrestled that one for several semesters.

 

[psprague]

Generate GUIDs with NewID(). Supposedly they are random too.