用存储过程写随机生成100个用户名和密码

CREATE PROCEDURE [dbl].[CreateRandUser]
4ZqKS K
YK5N*`VcN0AS51Testing软件测试网!?[|m~Lz,J

LJT?Ev0--生成随机用户名密码51Testing软件测试网\B.|0T0n
--创建测试环境51Testing软件测试网?$z&YJ3LZIsG
set nocount on51Testing软件测试网W?`qq0G

0dbN-M2Nu"oL.OWST0--创建字符集
!m p5m+G5~\*Ow:V0create table #151Testing软件测试网$\A%s7~G
(
zI Fm"^,w9f#H.G0     [rnum] varchar(5)51Testing软件测试网4e)a8rr[7P+B
)51Testing软件测试网t[zT~0q
declare  @i  int
:CN)dO1yT&W0set @i=051Testing软件测试网cv)R^Bv:O(T

~I4p`I)x0--插入０－９十个数字，不需要数字可注释下面代码51Testing软件测试网_1B?5V|3\2|*^
while @i<10
m#]{4P'K0begin
P GEc/f(H}t0     insert #1 select @i
T@|;Wj q0     set @i=@i+151Testing软件测试网'v jK%?:p#w1Nn-kf
end51Testing软件测试网 f%Kt6Z9EX2{
--插入A-Z26个字母
`w
y.F9})K6G;PYND$i0set @i=ascii('A')
TG MW!`H0while @i<=ascii('Z')
#rt/tv.et0begin
x#CD\9v3p~0    insert #i select char(@i)51Testing软件测试网QP1L1KN+z
    set @i=@i+151Testing软件测试网z"y \ Py
kU
end51Testing软件测试网*Rm`FH-t
v+A#NcQ
--创建目标表存储过程51Testing软件测试网$]@%W5mp7M#So
create table Result51Testing软件测试网&_
d Z-AC,]k
(51Testing软件测试网eL.G$X J
     ID int identity,username varchar(50),pwd varchar(50)
o}~2SVuS0)
7Cl&_ gN4v;mz5b0--插入数据51Testing软件测试网'C7C!V,?(SSN&\
declare @tmpuser varchar(100)51Testing软件测试网 _8Xy"~'K3P5?W
declare @tmppwd varchar(100)

R5s-Dj.Oz&zt0select @tmpuser='',@tmppwd='',@i=151Testing软件测试网#Um;JH*kSR
while @i<=100                   --生成１００条结果集51Testing软件测试网lh_*n z/c
begin51Testing软件测试网tfz9l#c?vj
    select @tmpuser=@tmpuser+rnum from (select top 5 rnum from #1 order by newid())A
r"V{d6@ ]0    select @tmppwd=@tmppwd+rnum from (select top 5 rnum from #1 order by newid())A
/r8Or?6U2\0    insert Result(username,pwd) select'admin_'+@tmpuser,'pwd_'+@tmppwd51Testing软件测试网;R zr2y-eI"S
    set @i=@i+151Testing软件测试网;HE~*l3wz1y$V
    set @tmpuser=''
e%j5H't}}b0    set @tmppwd=''51Testing软件测试网I;b7J%b-b'\w
end

2t+O7v@"h:^9E"A+v0--查看
5I7@7f D$hV0select * from result51Testing软件测试网(||sju5h-f#UZ
--删除测试环境
y,d&{:Ggcm0drop table #151Testing软件测试网8W`"[s7R
go51Testing软件测试网(m@5kC;I O7r C5m~?