Non repetable random number generation script

CREATE PROCEDURE non_repeat_rand  
(@from int, @to int,@restart int = 0)  
AS  
/**************************************************************************
Date Created            : 29/03/2006
Author                  : Ravi Lobo
Program Description     : Non repeatable random numbers

EXECUTION 
---------
DECLARE @from int
       ,@to   int
       ,@restart int  -- (0 or 1)

SELECT @from=10, @to=15
EXECUTE non_repeat_rand @from,@to

-- or

EXECUTE non_repeat_rand 10,15,1


**************************************************************************/
BEGIN   
SET NOCOUNT ON  

DECLARE @rn  int
       ,@sql nvarchar(1000)  
  
-- restart
IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE NAME LIKE '##seq'+convert(varchar,@@spid)) AND @restart = 1
BEGIN 
 SELECT   @sql='DROP TABLE ##seq'+convert(varchar,@@spid)
 EXECUTE (@sql)  
END 

IF NOT EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE NAME LIKE '##seq'+convert(varchar,@@spid))  
BEGIN   

 SELECT   @sql='CREATE TABLE ##seq'+convert(varchar,@@spid)+' (id int)'  
 EXECUTE (@sql)  
     
   WHILE @from <= @to  
   BEGIN   
     SELECT   @sql='INSERT ##seq'+convert(varchar,@@spid)+' SELECT '+convert(varchar,@from)  
     EXECUTE (@sql)  
    
     SELECT  @from = @from + 1  
   END   
END   
  
 SELECT @sql=N'SELECT TOP 1 @rn=id FROM ##seq'+convert(varchar,@@spid)+' ORDER BY newid()'  
 EXEC sp_executesql @sql, N'@rn int OUTPUT', @rn OUTPUT  
    
 SELECT @sql=N'DELETE FROM ##seq'+convert(varchar,@@spid)+' WHERE id=@rn'  
 EXEC sp_executesql @sql,N'@rn int',@rn=@rn  
  
 SELECT @rn  
  
END   

GO