¿debe funcionar?.........mmmmm, mira RAND() te regresa un valor flotante entre cero y uno. Entonces es claro el porque te marca el error de sintaxis.
Si tienes un Id consecutivo puedes generar el aleatorio en ASP entre 1 y el número máximo de registros, y solo buscar el número de Id que coincida con el aleatorio.
Ahora, tambien tienes otras alternativas, por ejemplo:
Código:
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
Código:
(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
(
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=173)
Saludos