Click here to Skip to main content
14,699,754 members
Please Sign up or sign in to vote.
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

# 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

1 solution

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:
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.
   
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/[^]
Code-Hunt 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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900