随机产生密码，insus.net总结了三个，并分别写成了存储过程。
第一个，
代码如下 复制代码
usp_randompassword create procedure [dbo].[usp_randompassword]
(
@length int = 8
)
as
begin
declare @randompassword nvarchar(max) = n'',@l int = 1
while @l begin
--随机产生每一位字符，ascii码48至122
declare @rndchar char(1) = char(round(rand() * (122-48+1) + 48,0))
--随机产生的字符不包括下面字符
if ascii(@rndchar) not in(58,59,60,61,62,63,64,91,92,93,94,95,96) -- : , ; , , ? ,@ , [ , , ] , ^ , _ , `
begin
set @randompassword = @randompassword + @rndchar
set @l = @l + 1
end
end
select @randompassword
end
第二个，
代码如下 复制代码
usp_randompassword create procedure [dbo].[usp_randompassword]
(
@length int = 8
)
as
begin
declare @randompassword nvarchar(max) = n'',@l int = 1
--随机密码将由下面字符串产生，数字0-9,大写字母a-z,小写字母a-z
declare @basestring varchar(255) = '0123456789abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
while @l begin
--61是变量@basestring的长度减一
set @randompassword = @randompassword + substring(@basestring, convert(int,round(rand() * 61 + 1,0)),1)
set @l = @l + 1
end
select @randompassword
end
第三个，
代码如下 复制代码
usp_randompassword create procedure [dbo].[usp_randompassword]
(
@length int = 8
)
as
begin
declare @randompassword nvarchar(max) = n''
declare @r tinyint,@l int = 1
while @l begin
set @r = round(rand() * 2, 0) --随机产生0,1,2整数
if @r = 0 --当变量为0时，将随机产生一位数字
set @randompassword = @randompassword + char(round(rand() * 9 + 48,0))
else if @r = 1 --当变量为1时，将随机产生一位大写字母
set @randompassword = @randompassword + char(round(rand() * 25 + 65,0))
else if @r = 2 --当变量为2时，将随机产生一位小写字母
set @randompassword = @randompassword + char(round(rand() * 25 + 97,0))
set @l = @l + 1
end
select @randompassword
end
最后一个也可以重构写成：
代码如下 复制代码
usp_randompassword create procedure [dbo].[usp_randompassword]
(
@length int = 8
)
as
begin
declare @randompassword nvarchar(max) = n'',@l int = 1
while @l begin
declare @r int = round(rand() * 2, 0)
set @randompassword = @randompassword + case @r
when 0 then char(round(rand() * 9 + 48,0))
when 1 then char(round(rand() * 25 + 65,0))
when 2 then char(round(rand() * 25 + 97,0)) end
set @l = @l + 1
end
select @randompassword
end
also reference: