Randomly selecting row from database

Selecting a Random Row From Databse:


Description:

If you want to select row randomly from the databse, you can use this.




Stored Procedure:


CREATE PROCEDURE PS_GetRandomProduct
AS
DECLARE @NoOfRows int
Begin
SELECT @NoOfRows = max(ProductId) FROM Products

SELECT QuantityPerUnit, ProductName FROM Products
WHERE ProductId = (SELECT CAST((RAND()* @NoOfRows) AS int) + 1)
End
GO

Explanation:


ProductId is the primary key of the table (Products). It is the identity field

(auto-incrementing).we are taking maximum value of identity field and assigning

this value into the variable @NoOfRows.A random number is then generated with a br>
maximum possible value of @NoOfRows.


<

The rand() function generates a random number between 0 and 1, which is multiplied

to the @NoOfRows variable.You may be wondering why 1 is added to the result. The

reason is that the rand() function may return 0 while the ProductId column starts at 1.



Regard

Prateek