Article

How To Generate (N) Digit Random Number Between Start And End Range In SQL Server

01 Jun 2018 Kamal Pratap
0 Comments 781 Views



Sometime we need to use unique random number into our application. Below we explain the logic to generate random number between two given range.

 

    
	Declare @TotalCard Numeric(18,0),@CardCounter Numeric(18,0),@RandomNumber Varchar(20)
	Select @TotalCard=100000,@CardCounter=1

	DECLARE @Upper Numeric(18,0);
	DECLARE @Lower Numeric(18,0)
 
	---- This will create a random number between 1000000000 and 9999999999
	SET @Lower = 1000000000 ---- The lowest random number
	SET @Upper = 9999999999 ---- The highest random number
	
	--Loop for number of card to be genrated
	WHILE(@CardCounter<=@TotalCard)
	BEGIN
			SELECT @RandomNumber = CAST(CAST(ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) AS Numeric) As Varchar)
			
			IF EXISTS(SELECT 1 FROM tbl_RandomNumber With(Nolock) where RandomNumber=@RandomNumber)
			BEGIN
				SET @RandomNumber = NULL
			END
			ELSE IF(LEN(@RandomNumber)<>10)
			BEGIN
				SET @RandomNumber = NULL
			END
			ELSE
			BEGIN
				INSERT INTO tbl_RandomNumber(RandomNumber)
				VALUES(@RandomNumber)				
			END
			
			WHILE(ISNULL(@RandomNumber,'')='')
			BEGIN
				SELECT @RandomNumber = CAST(CAST(ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) AS Numeric) As Varchar)
				
				IF EXISTS(SELECT 1 FROM tbl_RandomNumberDigit With(Nolock) where RandomNumber=@RandomNumber)
				BEGIN
					SET @RandomNumber = NULL
				END
				ELSE IF(LEN(@RandomNumber)<>10)
				BEGIN
					SET @RandomNumber = NULL
				END
				ELSE
				BEGIN					
					INSERT INTO tbl_RandomNumberDigit(RandomNumber)
					VALUES(@RandomNumber)		
				END
			END
			
			SET @CardCounter = @CardCounter + 1
				
	END	-- End of loop

 

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment