Technical Article

Non repetable random number generation script

,

Some time back i had the requirement for a SP which will give non repeatable random numbers with in a range.

Following SP accepts a range and gives the random numbers. Once the numbers are exhausetd NULL, will be the out put. In this case user can restart the random number generation by passing 1 as the third parameter.

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

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating