"Best Of" request randomizer

bestofRandomizer.sql

/*
Trying to randomize the records in dbo.BestOfRequestDetail, but preserve them in sets of 10 by iBestOfRequestID.
*/


--clean up overall
if object_id('tempdb..#newresults') is not null
    drop table #newresults;
if object_id('tempdb..#finalresults') is not null
    drop table #finalresults;
if object_id('tempdb..#temp') is not null
    drop table #temp;



--temp result sets
create table #newresults(
	iBestOfRequestDetailID int, 
	iBestOfRequestID int, 
	iRequestID int, 
	iStaffID int, 
	nOrder int, 
	iTermNumber int, 
	iSchoolID int
);    

--copy DB
insert into #newresults
	select * from dbo.BestOfRequestDetail;
	
-----------------------------------------------
-----------------------------------------------
-----------------------------------------------
-----------------------------------------------

RESTART: 
    
create table #temp(
	iBestOfRequestDetailID int, 
	iBestOfRequestID int, 
	iRequestID int, 
	iStaffID int, 
	nOrder int, 
	iTermNumber int, 
	iSchoolID int
);


--query here
insert into #temp 
	select iBestofRequestDetailID,iBestofRequestID,iRequestID,iStaffID,nOrder,iTermNumber,iSchoolID
	   from #newresults
	      where iBestOfRequestID=(Select MAX(iBestOfRequestID) from #newresults);

--cursor
declare @BestofRequestDetailID int;
declare @BestofRequestID int;
declare @RequestID int;
declare @StaffID int;
declare @nnOrder int;
declare @TermNumber int;
declare @SchoolID int;
declare @randomnumber varchar(50);

--counter
declare @icounter int;
set @icounter=1;

DECLARE test CURSOR FOR 
  select *,NEWID() from #temp order by NEWID();
    
open test;

fetch next from test 
   into @BestOfRequestDetailID, @BestOfRequestID, @RequestID, @StaffID, @nnOrder, @TermNumber, @SchoolID, @randomnumber;

	while @@FETCH_STATUS=0
	begin;
		
		     --insert into the result set
		     update dbo.BestOfRequestDetail SET 
				iBestOfRequestID=@BestOfRequestID, 
				iRequestID=@RequestID, 
				iStaffID=@StaffID, 
				nOrder=@icounter, 
				iTermNumber=@TermNumber, 
				iSchoolID=@SchoolID
					WHERE iBestOfRequestDetailID=@BestOfRequestDetailID;
		         
	      
		     print @icounter;
		     SET @icounter = @icounter+1;

				delete from #newresults where iBestOfRequestID = @BestofRequestID;
		     
		     fetch next from test
		     into @BestOfRequestDetailID, @BestOfRequestID, @RequestID, @StaffID, @nnOrder, @TermNumber, @SchoolID, @randomnumber;
  
	end;

	

--reset counter
SET @icounter=0;

close test;
deallocate test;

drop table #temp;

if exists(select * from #newresults) goto RESTART;

-----------------------------------------------
-----------------------------------------------
-----------------------------------------------
-----------------------------------------------

--test
select * from dbo.BestOfRequestDetail;