ASP.NET

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER
Procedure [dbo].[GetRandomOptionsold]
(@class varchar(max),
@Subject varchar(max),
@ExamType varchar(max),
@School varchar(max)
)
 
as
 
Begin
 
Create
Table #Options(ID int identity(1,1),Fields nvarchar(10))
 
Insert
into #Options
 
Select
COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME='Questions' And COLUMN_NAME like 'O%' Order By NEWID()
 
--Select * from #Options

Declare
@RandomCols nvarchar(100)
 
Set
@RandomCols=''
 
Select
@RandomCols=@RandomCols+Fields+' ,' from #Options
 
--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O1,')
 
+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))
 
--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O2,')
 
+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))
 
--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O3,')
 
+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))
 
--Select @RandomCols

Set
@RandomCols=(Select REPLACE(Substring(@RandomCols,0,CHARINDEX(' ,',@RandomCols)+2),' ,',' as O4,')
 
+
SUBSTRING(@RandomCols,CHARINDEX(' ,',@RandomCols)+2,LEN(@RandomCols)))
 
--Set @RandomCols=SUBSTRING(@RandomCols,0,len(@RandomCols))

--Select @RandomCols

Declare
@strQry nvarchar(MAX)
 
Set
@strQry=''
 
Set
@strQry='Select qno,que,'+@RandomCols+'ans from Questions where Class='''+@Class+''' and Subject='''+@Subject+''' and ExamType='''+@ExamType+''' and School='''+@School+''''
 
exec
(@strQry)
 
End