By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,730 Members | 1,567 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,730 IT Pros & Developers. It's quick & easy.

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

P: 20
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

Expand|Select|Wrap|Line Numbers
  1. DECLARE @intFlag INT
  2.  
  3. DECLARE @random INT
  4. DECLARE @upper INT
  5. DECLARE @lower INT
  6.  
  7. SET @intFlag = 1
  8.  
  9.  
  10. set @lower=1
  11. set @upper=100
  12.  
  13. WHILE (@intFlag <=10)
  14.  
  15. BEGIN
  16. select @random=Round(((@upper-@lower-1))*RAND()+@lower,0)
  17.  
  18.  
  19. PRINT @random
  20.  
  21. select * from science where ques_no=@random
  22.  
  23. SET @intFlag = @intFlag + 1
  24.  
  25. END
  26.  
  27. GO
  28.  
May 12 '13 #1
Share this Question
Share on Google+
4 Replies


weaknessforcats
Expert Mod 5K+
P: 9,197
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.
May 12 '13 #2

Rabbit
Expert Mod 10K+
P: 12,430
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.
May 12 '13 #3

ck9663
Expert 2.5K+
P: 2,878
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
May 13 '13 #4

P: 20
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.
May 19 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.