用SQL生成流水号

用SQL生成流水号
转：韦江涛    发表于2010年02月03日 09:38 阅读(4) 评论(0)

 
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_FillNumberWithZero]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_FillNumberWithZero]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_FormatDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_FormatDate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetNewFlowNumber]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_GetNewFlowNumber]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetNowDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_GetNowDate]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
生成流水号后面几位数字字符的相关函数
不足位数在左边用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


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
流水号函数相关函数
返回某个日期的格式化形式如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

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
获取一条新的单据流水号
-流水号格式为 @PrefixString+'-'+当前日期+4位顺序编号:CGRK-20070509-0005
*/

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












GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
获取当天日期
*/

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


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO