序号生成一例

Create VIEW [dbo].[v_GetDate]
AS
SELECT dt=CONVERT(VARCHAR(6),GETDATE(),12)

 

 

CreateFUNCTION [dbo].[f_er_NextBH]()    
RETURNS char(7)
AS
BEGIN
    DECLARE @dt CHAR(6)
    DECLARE @d CHAR(2)

    SELECT   @dt=dt FROM v_GetDate
    SELECT   @d=RTRIM(LTRIM(substring(@dt,2,1)))+RTRIM(LTRIM(case substring(@dt,3,2) when '10' then '0' when '11' then 'N' when '12' then 'D' else substring(@dt,4,1) end ))
    RETURN(
        SELECT @d+RTRIM(LTRIM(RIGHT(1000001+ISNULL(RIGHT(MAX(FlowNo),5),0),5) ))
        FROM er_InfoProduct  WITH(XLOCK,PAGLOCK)
        WHERE len(FlowNo)=7 and FlowNo like @d+'%')  
END


--    select dbo.f_er_NextBH()   

转载于:https://www.cnblogs.com/songrun/archive/2010/01/28/1658083.html