Updating a table using SP

[croydon]

I would appreciate some help with the following problem.

I have a table that must be displayed in a different order each week. So I am looking to create a SP to update a field in the table with a random number. The SP could then be scheduled to run overnight once a week.

I tried updating the table using rand() but each record received the same value.

This would be easy in VB6 or ASP.NET using a recordset, but I can't see how it can be done in SQL Server.

Any help would be appreciated.

 

[nigelrivett]

You can use a guid

update tbl set guid = newid()

then order by that.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[croydon]

But would this give me a change of display order each week?

 

[seweryng]

Hi,

solution 1:
You can use any of random number generators, like e.g. described in:

http://random.mat.sbg.ac.at/generators/

Simple one of them can be calculated as:
y(n+1) := A*y + B (mod p)

A,B are just any numbers
y(0) can be iplemented as cast(getdate() as int)
Therefore you will obtain different y(0) each week.
If there is no "mod" function in db, it can be implemented also as x/y - round(x/y). (y should not be too small)

solution 2:
x:= any id from the table (or whatever cast as int) + cast(getdate() as int),

then calculate
x/y - round(x/y), where y is any large number eg. 123456,

This should alter your order week after week,

Rgds,
Seweryn

 

[seweryng]

croydon,

Sorry, actually after doing small test the y from solution 2 should be = min(id),

Rgds,
Seweryn

 

[nigelrivett]

>> But would this give me a change of display order each week?

Yes the update statement will allocate new guids which will be random (as long as you aren't running on nt4).

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.