getting random records from a stored procedure

[andre]

hi,

is there a way to get for example 10 random records returned from a table
using only a stored procedure and not inline sql statement which use a cursror.

I was thinking to create a #temp table and going through it...
does anyone know or has done it before?

help is greatly appreciated.

Sincerely,
andre

[Randy Pearson]

I don't have any code, but here are some quick thoughts. First of all you
are going to want to use a WHILE loop combined with the RAND() function.
Next you'll need to determine the population size, or a proxy of that, to
be combined with RAND() to use a record selection.

If you have an identity column in the table, you could avoid cursors by finding
the maximum value of the identity column and multiplying the result of each
RAND by that value to get a pointer to a random record. Of course, you'll
need to confirm that this record exists (could have been deleted).

Without an identity column (or similar auto-incrementing integer field),
you'll need to use a cursor, but that cursor code can be inside the stored
procedure. The cursor will have to be KEYSET, so you can use @@CURSOR_ROWS
to get your population size, and then use FETCH ABSOLUTE nn to move to each
random record.

In either case, I would use the #temp table, and insert record into it until
you got up to 10 good ones. Then just SELECT * FROM #temp to return the result
set to the client.

Good luck,

-- Randy

>is there a way to get for example 10 random records returned from a table
>using only a stored procedure and not inline sql statement which use a cursror.
>
>I was thinking to create a #temp table and going through it...
>does anyone know or has done it before?

[Dave Kraft]

"andre" <arefay@im5.com> wrote in message news:39994ce2$1@news.devx.com...
>
> hi,
>
> is there a way to get for example 10 random records returned from a table
> using only a stored procedure and not inline sql statement which use a
cursror.
There's a relatively new article on www.4guysfromrolla.com that discusses
how to do this. Check it out.

Dave Kraft
TSR Solutions, Inc.