--USE [PaymentService]
GO
/****** Object: StoredProcedure [dbo].[sp_CreateRandomInviteCode] Script Date: 2016/1/18 9:11:20 ******/
DROP PROCEDURE [dbo].[sp_CreateRandomInviteCode]
GO
/****** Object: StoredProcedure [dbo].[sp_CreateRandomInviteCode] Script Date: 2016/1/18 9:11:20 ******/
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= FLOOR(RAND()* @DigitLength )
WHILE @BOOLBIT=1
BEGIN
WHILE @INDEX<@DigitLength
BEGIN
SET @RandomStr=@RandomStr+CONVERT(nvarchar, FLOOR(RAND()*10))
SET @INDEX=(@INDEX+1)
END
IF(@randomLetterPosition=0)
SET @InviteCode=@randomLetter + @RandomStr
ELSE
SET @InviteCode=SUBSTRING(@RandomStr,1,@randomLetterPosition)
+ @randomLetter + SUBSTRING(@RandomStr,@randomLetterPosition,@DigitLength - @randomLetterPosition)
IF(EXISTS(SELECT * FROM DBO.[DoctorProfile] WHERE InviteCode=@RandomStr))
BEGIN
SET @INDEX=0
SET @RandomStr=''
END
ELSE
BEGIN
SET @BOOLBIT=0
END
END
END
GO
执行结果: