/*----------------------------------Bulk generation of random string----------------------------------*/
--If the stored procedure is getrandstr in a known database, it is deleted (to facilitate repeated execution of this code)
if exists (SELECT * from sys.objects where name= ' getrandstr ' and type= ' P ')
drop proc Getrandstr
Go
--Random string stored procedure
Create proc Getrandstr
--@count is the number of random strings, @no is the resulting random string
(@count int, @no varchar () output)
As
Begin
[Email protected] is the value of each random character increment
DECLARE @randomstr varchar (1000)
[Email protected] is a sequence of characters that produce random characters
DECLARE @charpool varchar (1000)
--@i is the number of cycles
DECLARE @i int
[email protected] is where random characters are generated
DECLARE @counter integer
--Set random sequence
Set @charpool = ' 12345678910AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz '
--Loop Initial value
Set @i=1
--Random string initial value
Set @randomstr = ' '
--Cycle
While @i< @count
Begin
Here
--generates a random character position (an integer of 0-62)
Set @counter =cast (RAND () *100/1.6 As Integer)
--Regenerate random numbers if @counter = 0
If @counter <1 goto here
Set @[email protected]+substring (@charpool, @counter, 1)
--Cycle increment
Set @[email protected]+1
End
--The resulting six random characters are added with two fixed characters ' KY ' to make the required random string
Set @no = ' QQ ' +left (@randomstr, ISNULL (@count, 180))
Return
End
Go
--Call: For example, to generate 10 random strings
DECLARE @p int
Set @p=1
While @p<=10
Begin
DECLARE @substring varchar (8000)
--Call the stored procedure
EXEC getrandstr 8, @substring output
--Output random string
Print @substring
Set @[email protected]+1
End
(2016-09-01) SQL bulk generate random strings