Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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 11-May-13 22:27pm
shanboy384

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

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:
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.
  Permalink  
Comments
Maciej Los at 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 at 13-May-13 2:22am
   
+5 to both Griff and Maciej Los
shanboy at 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)

  Print Answers RSS
0 OriginalGriff 304
1 Sergey Alexandrovich Kryukov 255
2 Shweta N Mishra 216
3 Maciej Los 210
4 PIEBALDconsult 184
0 OriginalGriff 7,660
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,665


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 12 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100