When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
Technology Jobs
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, Queries


Question:

How can I pick a random record from a table, using a stored procedure?


[Print this FAQ]

Answer: Bill Wilkinson's FAQ describes how to return a random record from a table.
However, when you use a SQL Server database it's more efficient to use a stored procedure to return the records. For more information on using stored procedures read this article.

In this article Nathan Pond describes the use of a stored procedure to return a random record, but his method has some overhead because it creates a temporary table. This can be very ineffective when you have a large table.

My examples assume you have a table with an autoincrement ID field. It also covers the fact that there might be gaps in the ID field. (for example because some records were deleted)
If your table does not have an autoincrement ID field you might want to use a second table which has an autoincrement ID field and holds a pointer to the actual table.

Now Iíll show you the code, and try to explain every step:

CREATE PROCEDURE spGetRandomRecord

AS

--Declare local variables
DECLARE @counter int, @randno int, @uBound int, @lBound int

--You donít want to get the results of the interim
--SELECT statements passed to the recordset
--so you have to set NOCOUNT on SET NOCOUNT ON

--Set the upper and lowerbound for the random number
SELECT @uBound = Max(ID) FROM table
SELECT @lBound = Min(ID) FROM table

--Get a random number
SELECT @randno = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
SET @Counter = 0

--Keep trying until we find a record. This is necessary
--if there are gaps in the ID field.
WHILE @counter = 0
BEGIN
--Check if there is an record with the specified ID
IF EXISTS(SELECT ID FROM table WHERE id = @randno)
BEGIN
--The record exists, so get the complete record
SET NOCOUNT OFF
SELECT * FROM Table WHERE id = @randno
--Set the @counter variable to 1 to leave the WHILE loop
SET @counter = 1
END
ELSE
BEGIN
--The SELECT didn't return any records, so get a
--new random number and try again
SELECT @randno = Round(((@uBound - @lBound -1 ) * Rand() + @lBound), 0)
END
END

The above solution is suitable when you have minor gaps in the ID field. If the gaps are fairly large it might take too long to find a record. To avoid this you could try to find a record in a range of id's and select one of them.

(Rest of the procedure remains the same)

--Check if there is an record in a range of id's
--starting from the random number
IF EXISTS(SELECT ID FROM table WHERE id BETWEEN @randno and @randno + 25)
BEGIN
--There were some records found in the specified
--range, so select one of them SET NOCOUNT OFF
SELECT TOP 1 * FROM Table WHERE id BETWEEN @randno and @randno + 25
--Set the @counter variable to 1 to leave the WHILE loop
SET @counter = 1
END

Depending on the gaps in your id field you might want to change the 25 in something more appropriate for your situation, just test what works best in your situation.

Happy Programming!


FAQ posted by Dutch at 11/21/2001 7:46:30 AM to the Databases, Queries category. This FAQ has been viewed 66,022 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM

Software Developer / Programmer - Distributed Systems (NYC)
Next Step Systems
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article