alter procedure pro_RandomNum as begin declare @tbUser table (
id int identity(1,1) , FC_EaID varchar(50) null, FC_UserName varchar(50) null, FC_Pwd varchar(50) null, FC_ClearPwd varchar(50) ) insert into @tbUser select FC_EaID,FC_UserName,FC_Pwd,'' from TB_ExpertAccount declare @i int,@totalNum int set @i=1; select @totalNum=count(1) from @tbUser while(@i<=@totalNum) begin declare @randNum varchar(50) SELECT @randNum=convert(varchar(1), cast(ceiling(rand() * 9) as int))+'' +convert(varchar(1), cast(ceiling(rand() * 8) as int))+'' +convert(varchar(1), cast(ceiling(rand() * 9) as int))+'' +convert(varchar(1), cast(ceiling(rand() * 9) as int))+'' +convert(varchar(1), cast(ceiling(rand() * 8) as int))+'' +convert(varchar(1), cast(ceiling(rand() * 9) as int)) update @tbUser set FC_ClearPwd=@randNum,FC_Pwd=right(sys.fn_VarBinToHexStr(hashbytes('MD5',CONVERT(varchar(64),FC_Pwd))),32) where id=@i set @i=@i+1 end select * from @tbUser update b set b.FC_Pwd=a.FC_Pwd,b.FC_Creator=a.FC_ClearPwd from @tbUser as a left join TB_ExpertAccount as b on a.FC_EaID=b.FC_EaID end
go