sql获取随机列

USE [ExamDB]
GO
/****** Object:  StoredProcedure [dbo].[proc_RandAnswer]    Script Date: 03/08/2011 14:56:30  by fuxiang ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[RandAnswer]--答案随机排序
 @DepartId varchar(50),
 @ExamType varchar(50)
as
 declare @questionno varchar (50)
 declare @AA varchar (200)--选项A
 declare @BB varchar (200)--选项B
 declare @CC varchar (200)--选项C
 declare @DD varchar (200)--选项D
 --declare @errorcount int --错误计数器
 --set @errorcount=0
 --begin transaction--事务开始
--创建临时表
create table #temp(
  tid int identity(1,1),
  qno varchar(50),
  answer varchar(500)
)
--set @errorcount=@errorcount+@@ERROR
 --向临时表里插入数据
 insert into  #temp select QuestionNo,ItemA from QuestionInfo where DepartId=@DepartId and ExamType=@ExamType
 insert into  #temp select QuestionNo,ItemB from QuestionInfo where DepartId=@DepartId and ExamType=@ExamType
 insert into  #temp select QuestionNo,ItemC from QuestionInfo where DepartId=@DepartId and ExamType=@ExamType
 insert into  #temp select QuestionNo,ItemD from QuestionInfo where DepartId=@DepartId and ExamType=@ExamType
 --select * from #temp ORDER BY qno
 
 --set @errorcount=@errorcount+@@ERROR
    Declare Cur Cursor For select questionno from QuestionInfo  where DepartId=@DepartId and ExamType=@ExamType
    Open Cur
 Fetch next From Cur Into @questionno
 While @@fetch_status=0 
     begin
   --获取随机值
   select top 1 @AA=answer from  #temp where qno=@questionno order by NEWID()
   
   select top 1 @BB=answer from  #temp where qno=@questionno order by NEWID()
   while @BB=@AA
   Begin
   select top 1 @BB=answer from  #temp where qno=@questionno order by NEWID()
   End
   
   select top 1 @CC=answer from  #temp where qno=@questionno order by NEWID()
   while @CC=@AA or @CC=@BB
   Begin
   select top 1 @CC=answer from  #temp where qno=@questionno order by NEWID()
   End
   
   select top 1 @DD=answer from  #temp where qno=@questionno order by NEWID()
   while @DD=@AA or @DD=@BB or @DD=@CC
   Begin
   select top 1 @DD=answer from  #temp where qno=@questionno order by NEWID()
   End
   --set @errorcount=@errorcount+@@ERROR
   --更新表QuestionInfo
   update dbo.QuestionInfo set ItemA=@AA, ItemB=@BB, ItemC=@CC, ItemD=@DD
   where QuestionNo=@questionno and DepartId=@DepartId and ExamType=@ExamType
   --set @errorcount=@errorcount+@@ERROR
   
    Fetch Next From Cur Into @questionno

 end
 Close Cur  
 Deallocate Cur
 
 DROP TABLE #temp
 --if @errorcount<>0
 -- begin
 --  rollback transaction --事务回滚
 -- end
 --else
 -- begin
 --  commit transaction--提交事务
 -- end
 --select @AA,@BB,@CC,@DD
 
RandAnswer--答案随机排序
 @DepartId ='201',
 @ExamType='1111'