存储过程生辰6位验证码

CREATE TABLE [_ActivityCode]

(

 ID BIGINT PRIMARY KEY IDENTITY(1,1),

 ActivityCode CHAR(6)

)

GO

CREATE UNIQUE NONCLUSTERED INDEX Ix__ActivityCode_Name  ON dbo._ActivityCode (ActivityCode)

 

 

TRUNCATE TABLE [_ActivityCode]

 

DECLARE @InsertCount INT=100000

 

DECLARE @ValidCharacters VarCHAR(38) = 'abcdefghjkmnpqrstuvwxyz23456789';

 

 

DECLARE @i INT=1

IF object_id('tempdb..#TempCodes') IS NOT NULL

 BEGIN

  DROP TABLE #TempCodes

 END

Create TABLE #TempCodes

(

 ActivityCode Char(6)

 

)

WHILE @i<=@InsertCount*2

BEGIN

 DECLARE @Code CHAR(6)=SUBSTRING(@ValidCharacters,(CAST( floor(rand()*30) as INT))+1,1)+

  SUBSTRING(@ValidCharacters,(CAST( floor(rand()*30) as INT))+1,1)+

  SUBSTRING(@ValidCharacters,(CAST( floor(rand()*30) as INT))+1,1)+

  SUBSTRING(@ValidCharacters,(CAST( floor(rand()*30) as INT))+1,1)+

  SUBSTRING(@ValidCharacters,(CAST( floor(rand()*30) as INT))+1,1)+

  SUBSTRING(@ValidCharacters,(CAST( floor(rand()*30) as INT))+1,1)

  INSERT INTO #TempCodes SELECT @Code

  SET @i=@i+1

END

 

INSERT INTO dbo.[_ActivityCode]

(ActivityCode)

SELECT DISTINCT TOP(@InsertCount) T.ActivityCode

FROM #TempCodes T

 LEFT JOIN dbo.[_ActivityCode] A ON A.ActivityCode = T.ActivityCode

 WHERE A.ID IS null

 

SELECT DISTINCT ActivityCode FROM [_ActivityCode] WHERE LEN(ActivityCode)<6