How to store the records returned by a query using while loop in SQL server 2000

[shanboy]

I want to generate random questions from a table in sql server
i would like to use stored procedure whose input parameter will be integer that comes from web page(.aspx).

Below is the query to generate 10 random questions.
can i store the records in a temp table or array in sql server (i doubt if it is available) from where i can display them on a .aspx page using sqldata adapter in ASP.NET 2.0(VS 2005). Is there a better way to do?

Any guidance will be of great help to me
thanx

DECLARE @intFlag INT
 
DECLARE @random INT
DECLARE @upper INT
DECLARE @lower INT
 
SET @intFlag = 1
 
 
set @lower=1
set @upper=100
 
WHILE (@intFlag <=10)
 
BEGIN
select @random=Round(((@upper-@lower-1))*RAND()+@lower,0)
 
 
PRINT @random
 
select * from science where ques_no=@random
 
SET @intFlag = @intFlag + 1
 
END
 
GO
 

[weaknessforcats]

I believe you can declare a cursor for your SELECT. The query result is paced in the cursor, which is a table, and you can then FETCH from this cursor to see the rows.

BTW: SQL is not my strong suit but I thought I'd try to help.

[Rabbit]

You should just use a select statement and order by the rand. Don't forget to seed it with a variable factor, such as time, in combination with a unique id, such as your question number.

[ck9663]

You can do a select statement and directly save the result to another table using the INTO clause.

With respect to the randomization part, you can read this and this...

Watch out the RAND() function as it will return the same thing within a single statement.

Happy Coding!!!


~~ CK

[shanboy]

Thanks everyone for replying

@weaknessforcats

i am using stored procedure(SP).In that i have used 'insert into' in a temp table and retriving the records from there.
i would like to use these records as output parameter in the SP to display them in a aspx page.As far as i know we should give the data type of out parameter .
what will be the data type of these records table or any other type.