Generate Random Numbers with SQL server Procedure

0
28

 

Random numbers are used in a number of places in SQL Server DBs here is one proc for creating any number of Random numbers of variable length as supplied

 

–<START>

create proc GenerateRandomNumbers(@nos as int,@length as int)
as
/*
Author: www.meabi.com
Proc to create random numbers of your wish and length
accepts 2 parameters 1. length 2. number of numbers to be generated
this creates a table RandomNumbers for the numbers to save

*/
    –create RandomNumbers if doesnt exist
    IF not EXISTS (SELECT *    FROM sysobjects     WHERE xtype=’u’ AND name=’RandomNumbers’) create table RandomNumbers ( rno numeric)

    –loop variable
    declare @i as int
    select @i=0
    
                while (@i<@nos)
                begin
                –Inserting in to table
                INSERT into RandomNumbers select round(rand()*power (10,@length),0)
                select @i=@i+1 –loop increment
                end

–</END>

 

Result

189298
619002
471119
966622
793168
814421
194347
734917
547931
182735

This is saved in a table RandonNumbers in the current DB ( You need Create table permission for execution of this Proc)

LEAVE A REPLY

Please enter your comment!
Please enter your name here