本文章分享了几款关于三种随便密码生成sql存储过程，有需要学习的同学可以参考一下下哈。
随机产生密码，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:
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至253000106@qq.com举报，一经查实，本站将立刻删除。
发布者：PHP中文网，转转请注明出处：https://www.chuangxiangniao.com/p/1834196.html
