The following T-SQL query batch will show you how to generate random numbers in SQL Server. If the start number is greater than the end number, it will give a custom message and exit from the query batch.
--/////////////////////////////////////////// Declare @Start_Num int,@End_Num int set @Start_Num=1 set @End_Num=10 If @Start_Num>@End_Num Begin Print 'Invalid range, Starting number shoul be less than End Number' Return End SET NOCOUNT ON Declare @RecNum int, @CurrentRec int, @MyCount int Set @MyCount=@End_Num-@Start_Num+1 Create Table #tblTemp (CurrentRecNo int identity(1,1), RandomNumber int null) Set @CurrentRec=1 While @CurrentRec<=@MyCount Begin Insert Into #tblTemp (RandomNumber) Values (null) Set @CurrentRec=@CurrentRec+1 End Set @CurrentRec=1 While @CurrentRec<=@MyCount Begin Set @RecNum=rand()*@MyCount+@Start_Num If @Start_Num<0 Set @RecNum=@RecNum-1 Update #tblTemp Set RandomNumber=@RecNum Where CurrentRecNo=@CurrentRec And @RecNum Not In (Select RandomNumber From #tblTemp Where RandomNumber Is Not Null) If @@rowcount>0 Set @CurrentRec=@CurrentRec+1 End Select RandomNumber From #tblTemp Drop Table #tblTemp --/////////////////////////////////////////// Change @Start_Num and @End_Num for different ranges.
Jorge C. writes: Here's
1) If the value of @Start_Num (=1 ) and @End_Num (=10 ) are established in the script's body, the validation of their values is not necesary. This would be necesary if we pass these two values as parameters; and what happens if @Start_Num = @End_Num in the validation?
2) The statement on line 12 (Create Table) must be preceded by the statement (Drop Table) in line 30, that's drop and create the table when we need it. If we need to use the same table after running the batch (as happens in line 29), it's not better have it enabled--the inconvenience is that we need to check first if the table exists.
-- if exist table then -- begin Drop Table #tblTemp -- end Create Table #tblTemp (CurrentRecNo int identity(1,1), RandomNumber int null)
3) On line 12, why do we want to create the field CurrentRecNo as identity? A well-known set of unique random numbers (just RandomNumber) or a list of pair values CurrentRecNo and RandomNumber:
Create Table #tblTemp (CurrentRecNo int identity(1,1), RandomNumber int null)
4) The while loop on lines 14 to 18 is not necesary--this will be replaced with the one on lines 19 to 28.
5) On line 16 the ramdom value must be Integer and between @Start_Num and @End_Num the while loop on lines 19 to 28, this will be simplified like this:
11> Set @MyCount = @End_Num - @Start_Num + 1 ... 19> Set @CurrentRec=1 While @CurrentRec <= @MyCount Begin Set @RecNum = CAST((rand()* @MyCount) AS int) + @Start_Num Insert Into #tblTemp (RandomNumber) Values (@RecNum ) Set @CurrentRec=@CurrentRec+1 End
Thus, the script will be:
DECLARE @Start_Num int , @End_Num int SET @Start_Num=1 SET @End_Num=10 IF @Start_Num >= @End_Num BEGIN PRINT 'Invalid range, Starting number shoul be less than End Number' RETURN END SET NOCOUNT Off DROP TABLE #tblTemp CREATE TABLE #tblTemp (CurrentRecNo int IDENTITY(1,1), RandomNumber int null) DECLARE @RecNum int, @CurrentRec int, @MyCount int SET @MyCount = ( @End_Num - @Start_Num ) + 1 SET @CurrentRec = 1 WHILE @CurrentRec <= @MyCount BEGIN SET @RecNum = CAST((RAND() * @MyCount) AS int) + @Start_Num INSERT INTO #tblTemp (RandomNumber) VALUES (@RecNum) SET @CurrentRec = @CurrentRec + 1 END SELECT RandomNumber FROM #tblTemp
Create and drop (or drop and create), is not really a very good way to ensure that the table is empty, right? Why not just test if the table exists? If it's not, then create it. If it exists, then delete it. And what happens if another user is working on it?
I'm a novice, but these are my impressions. Am I wrong?
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in January 2002