Sql Server中利用自定义函数完成单据流水号的设计

流水号是现在各类系统中单据的必备字段,因为流水号很容易标识一个新的单据.之前我也做了这么一个系统,流水号的格式为:单据前缀+业务日期+几位顺序编号.知道了流水号的固定格式,设计流水号就非常方便了.在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')是不是非常方便?