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