流水號是現在各類系統中單據的必備字段,因為流水號很容易標識一個新的單據.之前我也做了這么一個系統,流水號的格式為:單據前綴+業務日期+幾位順序編號.知道了流水號的固定格式,設計流水號就非常方便了.在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+@dayEND

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

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

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

CREATE VIEW dbo.v_DateNowASSELECT GETDATE() AS NowDate

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