USE [TEST]

GO

/****** Object: UserDefinedFunction [dbo].[RANDTENNUMS] Script Date: 2019/7/23 15:40:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE function [dbo].[RANDTENNUMS]()

returns varchar(10)

as

begin

DECLARE @i int

DECLARE @flag int

DECLARE @SerialNumber nvarchar(20)

DECLARE @RANDS Numeric(38,38)

--初始化设定

SET @i=1

SET @SerialNumber = ''

--生成10位随机码

WHILE @i<11

BEGIN

--设置随机，这个随机会选择字母（大小写）还是数字

SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]

SET @flag=ceiling(@RANDS *3)

IF @flag=1

BEGIN

--随机字母（大写去除大写的O）

SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]

WHILE char(65+ceiling(@RANDS * 25)) = 'O'

BEGIN

SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]

END

SELECT @SerialNumber=@SerialNumber+char(65+ceiling(@RANDS * 25))

END

else if @flag=2

begin

--随机字母（小写去除小写的o）

SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]

WHILE char(97+ceiling(@RANDS * 25)) = 'o'

BEGIN

SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]

END

select @SerialNumber=@SerialNumber+char(97+ceiling(@RANDS * 25))

end

else begin

--随机数字 1至9的随机数字(整数)

WHILE cast(ceiling(@RANDS * 9) as varchar(1)) = '0'

BEGIN

SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]

END

SELECT @RANDS = RAND_A FROM [dbo].[VW_RAND]

select @SerialNumber=@SerialNumber+cast(ceiling(@RANDS * 9) as varchar(1))

end

--进行下一个循环

SET @i=@i+1

END

RETURN @SerialNumber;

END