流水號是現在各類系統中單據的必備字段,因為流水號很容易標識一個新的單據.
之前我也做了這么一個系統,流水號的格式為:單據前綴+業務日期+幾位順序編號.
知道了流水號的固定格式,設計流水號就非常方便了.
在SqlServer中,我們可以通過客戶端程序來生成新的流水號,也可以利用存儲過程來生成.
在實際的項目中,我覺得利用自定義函數來生成非常方便,方便存儲過程調用,也方便客戶端的調用.
說了一大堆廢話,來看代碼吧.
1.主調用函數,由于在設計過程中有很多類似單據表,而且每個單據表包含了一個相同的流水號字段,所以為了方便代碼調用,提供了對應表的輸入參數.
/**//*
獲取一條新的單據流水號
-流水號格式為 @PRefixString+'-'+當前日期+4位順序編號:CGRK-20070509-0001
*/

CREATE FUNCTION dbo.fn_GetNewFlowNumber
(
@SheetTableName varchar(50)
)
RETURNS varchar(50)  AS 
BEGIN

    --流水號前綴
    declare @PrefixString varchar(50)
    --流水號后綴數字的位數
    declare @PostfixLength int   
    --定義好當日單據所有的流水號數據表
    declare @Table table(SheetNo varchar(50))
    --1.取得單據的最后一條SheetNo
    IF @SheetTableName=null OR @SheetTableName=''
        return ''
    /**//*庫存部分*/
    --其他入庫
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_StoreInSheet')
        BEGIN
            SET @PrefixString='QTRK'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_StoreInSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --其他出庫
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_StoreOutSheet')
        BEGIN
            SET @PrefixString='QTCK'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_StoreOutSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
           
        END
    --轉倉
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_StoreTransferSheet')
        BEGIN
            SET @PrefixString='CKZC'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_StoreTransferSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --盤點
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_StoreCheckSheet')
        BEGIN
            SET @PrefixString='CKPD'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_StoreCheckSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    /**//*采購單據操作部分*/
    --請購單
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_PurchaseRequestSheet')
        BEGIN
            SET @PrefixString='QGD'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseRequestSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --采購訂單
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_PurchaSEOrderSheet')
        BEGIN
            SET @PrefixString='CGDD'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseOrderSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL

        END
    --采購詢價單
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_PurchaseQuotationSheet')
        BEGIN
            SET @PrefixString='CGXJ'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseQuotationSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --采購入庫單
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_PurchaseInSheet')
        BEGIN
            SET @PrefixString='CGRK'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseInSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --采購退貨
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_PurchaseReturnSheet')
        BEGIN
            SET @PrefixString='CGTH'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchaseReturnSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --付款單
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_PurchasePaymentSheet')
        BEGIN
            SET @PrefixString='CGFK'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_PurchasePaymentSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
       
    /**//*銷售單據操作部分*/
    --銷售詢價
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_SaleQuotationSheet')
        BEGIN
            SET @PrefixString='XSXJ'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SaleQuotationSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --銷售訂單
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_SaleOrderSheet')
        BEGIN
            SET @PrefixString='XSDD'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SaleOrderSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --銷售出庫
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_SaleOutSheet')
        BEGIN
            SET @PrefixString='XSCK'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SaleOutSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --銷售退貨
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_SaleReturnSheet')
        BEGIN
            SET @PrefixString='XSTH'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SaleReturnSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END
    --銷售付款
    ELSE IF  LOWER(@SheetTableName)=LOWER('AT_SalePaymentSheet')
        BEGIN
            SET @PrefixString='XSFK'
            SET @PostfixLength=4
            INSERT INTO @Table(SheetNo) SELECT SheetNo FROM AT_SalePaymentSheet WHERE DateDiff(d,CreateTime,dbo.fn_getNowDate())=0 OR CreateTime=NULL
        END

    /**//*不屬于任何單據，返回空的單號*/
    ELSE
        RETURN ''
   
    /**//*
    存在一個當日同前綴的流水號的條件：
    1.流水號總長度相同
    2.相同的流水號前綴
    3.相同的中間日期部分           
    */
    --當日日期部分字符串
    declare @DateString varchar(8)
    SET @DateString=dbo.fn_FormatDate(dbo.fn_GetNowDate())
    --記錄中最后一條流水號
    declare @LastSheetNo varchar(50)
    /**//*--定義好相關參數，比較是否有相同的流水號前綴*/
    --存在，獲取最后一條流水+1           
    SELECT TOP 1 @LastSheetNo=SheetNO FROM @Table WHERE
         LEN(SheetNO)=len(@PrefixString)+10+@PostfixLength
         AND  LEFT(SheetNO,len(@PrefixString+'-'))=@PrefixString+'-'
        AND LEFT(SheetNO,len(@PrefixString+'-'+@DateString+'-'))=@PrefixString+'-'+@DateString+'-'
        ORDER BY  SheetNo DESC
    --return 'ssss'
    IF @LastSheetNo=NULL
        return @PrefixString+'-'+@DateString+'-'+dbo.fn_FillNumberWithZero(1,@PostfixLength)
    ELSE
        return @PrefixString+'-'+@DateString+'-'+dbo.fn_FillNumberWithZero(convert(int,right(@LastSheetNo,@PostfixLength))+1,@PostfixLength)
   
    return ''

END

2.相關函數

/**//*
生成流水號后面幾位數字字符的相關函數
不足位數在左邊用0填充
*/
CREATE FUNCTION dbo.fn_FillNumberWithZero
(
--填充的數字
@num int,
--總位數
@len int
)
RETURNS varchar(50) AS 
BEGIN
    --如果傳入的流水號大于總的長度，那么直接返回流水號字符串格式
    if(len(Convert(varchar(50),@num))>@len)
        return Convert(varchar(50),@num)
    ELSE
        BEGIN
            --需要填充0的位數
            declare @NeedFillLen int
            set @NeedFillLen=@Len-len(Convert(varchar(50),@num))
            --獲取需要填充的0的字符串
            declare @i int
            set @i=0
            declare @temp varchar(50)
            set @temp=N''
            while @i<@NeedFillLen
                BEGIN
                    SET @temp=@temp+'0'
                    SET @i=@i+1
                END
            --返回組后的字符串
            return @temp+Convert(varchar(50),@num)
        END
    return ''
END

/**//*
流水號函數相關函數
返回某個日期的格式化形式如20070509

*/
CREATE FUNCTION dbo.fn_FormatDate(@Date datetime)
RETURNS char(8) AS 
BEGIN
    declare @year char(4)
    declare @month char(2)
    declare @day char(2)
   
    set @year=convert(char(4),year(@Date))
    set @month=convert(char(4),month(@Date))
    set @day=convert(char(4),day(@Date))
   
    if len(@month)=1
        set @month=N'0'+@month
    if len(@day)=1
        set @day=N'0'+@day
    return @year+@month+@day
END

/**//*
獲取當天日期
*/

CREATE FUNCTION dbo.fn_GetNowDate()
RETURNS DateTime AS 
BEGIN
    declare @nowDate datetime
    select @nowDate=NowDate FROM v_DateNow
    return @nowDate
END

注意這里由于sqlserver的自定義函數無法直接獲取日期(無法調用getdate()函數),所以我們通過視圖的方式來獲取服務器的時間.視圖如下:
/**//*
獲取當前系統日期
這個視圖主要供自定義函數調用，
切勿刪除！！！！！！！
*/

CREATE VIEW dbo.v_DateNow
AS
SELECT GETDATE() AS NowDate

全部函數完畢.如何調用呢?
很簡單:
比如需要入庫單的新流水號:
select dbo.fn_GetNewFlowNumber('AT_StoreCheckSheet')
是不是非常方便?