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

Question

0.00/5 (No votes)

See more:

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

SQL

# 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

Posted 11-May-13 21:27pm

shanboy

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-05-11T21:33:00

Solution 1

Yes - just create a temporary table and insert the rows into that. You can then select the temporary table content at the end of the query.
For example:

SQL

DECLARE @M int
SET @M = 1
DECLARE @TAB TABLE ([Month No] INT, [Month Name] VARCHAR(20))
WHILE (@M <=12)
BEGIN
INSERT INTO @TAB VALUES(@M, DATENAME(Month, CAST('2000-' + CAST(@M AS VARCHAR(2)) + '-1' AS DATE)))
SET @M = @M + 1
END
SELECT * FROM @TAB

Creates a table containing month number and name, and fills it, returning the whole table at the end.

Posted 11-May-13 21:33pm

OriginalGriff

Comments

Maciej Los 12-May-13 15:38pm

My 5, but i think OP need some explanation, because in above example you use a table variable, not a temporary table.

@Shanboy, please, see: http://www.mssqltips.com/sqlservertip/1556/differences-between-sql-server-temporary-tables-and-table-variables/[^]

[no name] 13-May-13 2:22am

+5 to both Griff and Maciej Los

shanboy 19-May-13 7:57am

Thank you guys for taking your valuable time to reply.

@OriginalGriff As suggested by you i am inserting them into a temp table and retrieving the records from there
Thank you.