USE [DB]
GO
/****** Object: UserDefinedFunction [dbo].[GetNewPayOrderCode] Script Date: 03/12/2013 16:26:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetNewPayOrderCode]
(
@Date DATETIME
)
RETURNS VARCHAR(16)
AS
BEGIN
Declare @Year Char(4) -- 年月
Declare @Month Char(2) -- 月
Declare @Day Char(2) -- 日
Declare @MaxPayCode Char(4) -- 最大的流水号
Declare @NewPayCode char(16) -- 返回新流水号
Set @Year = Substring(Convert(Char(10), @Date, 112), 1, 4)
Set @Month = Substring(Convert(Char(10), @Date, 112), 5, 6)
Set @Day = Substring(Convert(Char(10), @Date, 112), 7, 8)
Declare @Prefix VARCHAR(50) -- 前缀
SELECT @Prefix = ConfigValue FROM LKConfig WHERE ConfigKey='OrderCodePrefix'
SELECT @MaxPayCode = Right(Max(Code), 4) FROM PayOrder
WHERE LEFT(Code, 11) = @Prefix + @Year + @Month + @Day
IF IsNull(@MaxPayCode, '') = ''
BEGIN
Set @NewPayCode = @Prefix + @Year + @Month + @Day + '0001'
END
ELSE
BEGIN
Set @NewPayCode = @Prefix + @Year + @Month + @Day + Right('0000' + CAST(CAST(@MaxPayCode AS INT)+ 1 AS Varchar(4)), 4)
END
RETURN @NewPayCode
END
GO
