流水號(hào)是現(xiàn)在各類系統(tǒng)中單據(jù)的必備字段,因?yàn)榱魉?hào)很容易標(biāo)識(shí)一個(gè)新的單據(jù).之前我也做了這么一個(gè)系統(tǒng),流水號(hào)的格式為:單據(jù)前綴+業(yè)務(wù)日期+幾位順序編號(hào).知道了流水號(hào)的固定格式,設(shè)計(jì)流水號(hào)就非常方便了.在SqlServer中,我們可以通過客戶端程序來生成新的流水號(hào),也可以利用存儲(chǔ)過程來生成.在實(shí)際的項(xiàng)目中,我覺得利用自定義函數(shù)來生成非常方便,方便存儲(chǔ)過程調(diào)用,也方便客戶端的調(diào)用.說了一大堆廢話,來看代碼吧.1.主調(diào)用函數(shù),由于在設(shè)計(jì)過程中有很多類似單據(jù)表,而且每個(gè)單據(jù)表包含了一個(gè)相同的流水號(hào)字段,所以為了方便代碼調(diào)用,提供了對(duì)應(yīng)表的輸入?yún)?shù)./**//*獲取一條新的單據(jù)流水號(hào)-流水號(hào)格式為 @PrefixString+'-'+當(dāng)前日期+4位順序編號(hào):CGRK-20070509-0001*/

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

--流水號(hào)前綴 declare @PrefixString varchar(50) --流水號(hào)后綴數(shù)字的位數(shù) declare @PostfixLength int; --定義好當(dāng)日單據(jù)所有的流水號(hào)數(shù)據(jù)表 declare @Table table(SheetNo varchar(50)) --1.取得單據(jù)的最后一條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 --轉(zhuǎn)倉 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 --盤點(diǎn) 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 /**//*采購單據(jù)操作部分*/ --請(qǐng)購單 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 --采購詢價(jià)單 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 --采購?fù)素?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 /**//*銷售單據(jù)操作部分*/ --銷售詢價(jià) 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

/**//*不屬于任何單據(jù)，返回空的單號(hào)*/ ELSE RETURN '' /**//* 存在一個(gè)當(dāng)日同前綴的流水號(hào)的條件： 1.流水號(hào)總長(zhǎng)度相同 2.相同的流水號(hào)前綴 3.相同的中間日期部分; */ --當(dāng)日日期部分字符串 declare @DateString varchar(8) SET @DateString=dbo.fn_FormatDate(dbo.fn_GetNowDate()) --記錄中最后一條流水號(hào) declare @LastSheetNo varchar(50) /**//*--定義好相關(guān)參數(shù)，比較是否有相同的流水號(hào)前綴*/ --存在，獲取最后一條流水+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.相關(guān)函數(shù)

/**//*生成流水號(hào)后面幾位數(shù)字字符的相關(guān)函數(shù)不足位數(shù)在左邊用0填充*/CREATE FUNCTION dbo.fn_FillNumberWithZero(--填充的數(shù)字@num int,--總位數(shù)@len int)RETURNS varchar(50) AS; BEGIN --如果傳入的流水號(hào)大于總的長(zhǎng)度，那么直接返回流水號(hào)字符串格式 if(len(Convert(varchar(50),@num))>@len) return Convert(varchar(50),@num) ELSE BEGIN --需要填充0的位數(shù) 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

/**//*流水號(hào)函數(shù)相關(guān)函數(shù)返回某個(gè)日期的格式化形式如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

/**//*獲取當(dāng)天日期*/

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

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

CREATE VIEW dbo.v_DateNowASSELECT GETDATE() AS NowDate

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