sql函数 得到 由 年月日时分秒+三位内的随机数 组成的随机数
由于函数内不能调用GetDate()以及Rand()这样的不确定函数
所以只有变通的去实现
年月日时分秒 就直接由GetDate()做为参数 传入该Function
而1~1000间的随机数 就借助于存储随机数的 视图来实现
--创建视图 V_RandNum
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.V_RandNum
AS
SELECT CAST(RAND() * (1000 - 1) + 1 AS INT) AS P2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.V_RandNum
AS
SELECT CAST(RAND() * (1000 - 1) + 1 AS INT) AS P2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--创建函数 FN_GetRandNum
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION FN_GetRandNum(@DT DATETIME)
RETURNS BIGINT
AS
BEGIN
--@P1 年月日时分秒
DECLARE @P1 VARCHAR(20)
SELECT @P1 = CONVERT(VARCHAR(20),@DT,20)
SET @P1 = REPLACE(@P1,'-','')
SET @P1 = REPLACE(@P1,':','')
SET @P1 = REPLACE(@P1,' ','')
--@P2 随机数
DECLARE @P2 VARCHAR(3)
SELECT @P2=P2 FROM V_RandNum
--@R 返回的值
DECLARE @R CHAR(20)
SET @R =@P1 + @P2
RETURN CAST(@R AS BIGINT)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION FN_GetRandNum(@DT DATETIME)
RETURNS BIGINT
AS
BEGIN
--@P1 年月日时分秒
DECLARE @P1 VARCHAR(20)
SELECT @P1 = CONVERT(VARCHAR(20),@DT,20)
SET @P1 = REPLACE(@P1,'-','')
SET @P1 = REPLACE(@P1,':','')
SET @P1 = REPLACE(@P1,' ','')
--@P2 随机数
DECLARE @P2 VARCHAR(3)
SELECT @P2=P2 FROM V_RandNum
--@R 返回的值
DECLARE @R CHAR(20)
SET @R =@P1 + @P2
RETURN CAST(@R AS BIGINT)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--调用该函数
SELECT TestDB.dbo.FN_GetRandNum(GetDate())
--结果为
20070917143820413
posted on 2007-09-17 14:41 freeliver54 阅读(2241) 评论(1) 编辑 收藏 举报