sql server 生成随机不重复的字符串

来源：互联网发布：c语言文件结束符编辑：程序博客网时间：2024/04/29 23:35
 use your DB_NAMEGO/****** Object:  StoredProcedure [dbo].[sp_CreateRandomInviteCode]    Script Date: 2016/1/20 14:41:10 ******/DROP PROCEDURE [dbo].[sp_CreateRandomInviteCode]GO/****** Object:  StoredProcedure [dbo].[sp_CreateRandomInviteCode]    Script Date: 2016/1/20 14:41:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*    DECLARE@return_value int,@InviteCode nvarchar(max)EXEC@return_value = [dbo].[sp_CreateRandomInviteCode]@DigitLength = 5,@InviteCode = @InviteCode OUTPUTSELECT@InviteCode as N'@InviteCode'--SELECT'|' + @InviteCode + '|' as N'@InviteCode'--SELECT'Return Value' = @return_value--select char(65)--select char(90)--SELECT CHAR(65 + FLOOR(RAND()*26))--select  FLOOR(RAND()*5)*/CREATE proc [dbo].[sp_CreateRandomInviteCode](@DigitLength INT=NULL,@InviteCode VARCHAR(MAX)  output)ASBEGIN   DECLARE @INDEX INT       DECLARE @SQL NVARCHAR(MAX)   DECLARE @RowCounts int   DECLARE @BOOLBIT BIT     DECLARE @RandomStr NVARCHAR(MAX)   DECLARE @RandomNum int   DECLARE @randomLetter varchar(1)   DECLARE @randomLetterPosition INT   SET @BOOLBIT=1   SET @RandomStr=''   SET @INDEX=0   IF(@DigitLength IS NULL OR @DigitLength<=0)SET @DigitLength=1    --SELECT @randomLetter= CHAR(65 + FLOOR(RAND()*26))    SELECT @randomLetterPosition=0  -- SELECT @randomLetterPosition= FLOOR(RAND()* @DigitLength )    WHILE @BOOLBIT=1   BEGIN   WHILE @INDEX<@DigitLength   BEGIN     SET @RandomStr=@RandomStr+CONVERT(nvarchar, FLOOR(RAND()*10))  SET @INDEX=(@INDEX+1)   END SELECT @randomLetter= CHAR(65 + FLOOR(RAND()*26))IF(@randomLetter='O' OR @randomLetter='I')BEGIN --PRINT 'CONTINUE'  CONTINUEEND    SET @InviteCode=@randomLetter + @RandomStr     --IF(@randomLetterPosition=0) --SET @InviteCode=@randomLetter + @RandomStr     --ELSE --SET @InviteCode=SUBSTRING(@RandomStr,1,@randomLetterPosition) --+ @randomLetter + SUBSTRING(@RandomStr,@randomLetterPosition,@DigitLength - @randomLetterPosition)    IF(EXISTS(SELECT * FROM DoctorInviteCode WHERE InviteCode=@RandomStr))   BEGIN      SET @INDEX=0  SET @RandomStr=''     END   ELSE   BEGIN       SET @BOOLBIT=0    END    END    ENDGO
运行结果：
（结束）
0 0