use your DB_NAME
GO
/****** 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
DECLARE @return_value int,
@InviteCode nvarchar(max)
EXEC @return_value = [dbo].[sp_CreateRandomInviteCode]
@DigitLength = 5,
@InviteCode = @InviteCode OUTPUT
SELECT @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
)
AS
BEGIN
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'
CONTINUE
END
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
END
GO
运行结果:
(结束)