【转载】SQLServer存储过程生成随机英文数字字符串

 

 

/****** Object:  StoredProcedure [dbo].[GetRandStr]    Script Date: 2020/3/24 11:20:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[GetRandStr]
  (@COUNT INT, @OUTSTR VARCHAR(8) OUTPUT) -- @COUNT是随机字符串个数，@OUTSTR则是最终生成的随机字符串
as

BEGIN

  DECLARE @RandomStr VARCHAR(100), @CharPool VARCHAR(62) --@RandomStr是每次随机的字符累加的值，@CharPool是产生随机字符的字符序列

  DECLARE @Idx INTEGER, @Counter INTEGER -- @Idx是循环次数，@Counter 是产生随机字符的位置

  SET @CharPool = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' --设置随机序列

  SET @Idx = 1 --循环初始值

  SET @RandomStr = '' -- 随机字符串初始值

  WHILE @Idx <= @COUNT 

    BEGIN

      HERE:

      SET @Counter = CAST(RAND() * 100 / 1.61 AS INTEGER) --产生随机字符位置 (0-62的整数)

      IF @Counter < 1 GOTO HERE -- 如果@Counter = 0 就重新生成随机数

      SET @RandomStr = @RandomStr + SUBSTRING(@CharPool, @Counter ,1) --在随机序列中找到生成的随机位置的字符

      SET @Idx = @Idx + 1 --循环递增

    END

    SET @OUTSTR = 'C' + LEFT(@RandomStr,ISNULL(@COUNT,7)) --最后将生成的六个随机字符加上两个固定字符'GC'，组成所需的随机字符串

  RETURN

END


GO

 

  

 转载于http://blog.sina.com.cn/s/blog_9f39f0c70101ff0q.html