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)



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.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