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

核心提示：流水号是现在各类系统中单据的必备字段,因为流水号很容易标识一个新的单据.之前我也做了这么一个系统,流水号的格式为:单据前缀+业务日期+几位顺序编号.知道了流水号的固定格式,设计流水号就非常方便了.在SqlServer中,我们可以通过客户端程序来生成新的流水号,也可以利用存储过程来生成.在实际的项目中,我觉得利用自定义函

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