| 订阅 | 在线投稿
分享
 
 
 

一个写的很不错的获取单据编号存储过程

来源:互联网  宽屏版  评论
2008-06-01 02:15:16

获取单据编号的存储过程:

示例:

CREATE TABLE [dbo].[BillNo](

[Id] [int] IDENTITY(1,1) NOT NULL,

[BillName] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,

[MaxNo] [int] NULL,

[MaxDate] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,

[Prefix] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,

[NumBit] [int] NULL CONSTRAINT [DF_BillNo_NumBit] DEFAULT ((0)),

[Style] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

[Memo] [text] COLLATE Chinese_PRC_CI_AS NULL,

[CreateDate] [datetime] NULL CONSTRAINT [DF_BillNo_CreateDate] DEFAULT (getdate()),

[EditDate] [datetime] NULL,

[State] [int] NULL CONSTRAINT [DF_BillNo_State] DEFAULT ((0)),

CONSTRAINT [PK_BillNo] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE PROCEDURE [dbo].[p_GetBillNo] --产生按年月日排列的档案号

@BillName varchar(20),

@No varchar(20) output, --为产生的档案号

@NumBit int = 4 --编号位数

AS

DECLARE @MaxNo int,

@MaxDate varchar(20),

@Prefix varchar(20),

@xNumBit int,

@CurrDate varchar(12),

@FILL_ZERO int,

@iMaxNo int

SET @FILL_ZERO = 100000000

SET @CurrDate = CONVERT(VARCHAR(8), GETDATE(),112)

SELECT @MaxNo = MaxNo, @MaxDate = MaxDate, @Prefix = Prefix,

@xNumBit = NumBit FROM BillNo WITH(XLOCK) WHERE BillName = @BillName

IF @@ROWCOUNT <> 1 RETURN -1

IF @xNumBit <> 0 SET @NumBit = @xNumBit

IF @MaxDate <> @CurrDate OR @MaxDate IS NULL OR @MaxNo IS NULL

SET @iMaxNo = 1

ELSE

SET @iMaxNo = @MaxNo + 1

UPDATE BillNo

SET MaxNo = @iMaxNo, MaxDate = @CurrDate,

EditDate = GetDate() WHERE BillName = @BillName

IF @@ROWCOUNT <> 1 RETURN -1

SET @No = COALESCE(@Prefix, '') + @CurrDate + RIGHT

(CAST(@FILL_ZERO + @iMaxNo AS VARCHAR), @NumBit)

RETURN 1

获取单据编号的存储过程: 示例: CREATE TABLE [dbo].[BillNo]( [Id] [int] IDENTITY(1,1) NOT NULL, [BillName] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL, [MaxNo] [int] NULL, [MaxDate] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [Prefix] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [NumBit] [int] NULL CONSTRAINT [DF_BillNo_NumBit] DEFAULT ((0)), [Style] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [Memo] [text] COLLATE Chinese_PRC_CI_AS NULL, [CreateDate] [datetime] NULL CONSTRAINT [DF_BillNo_CreateDate] DEFAULT (getdate()), [EditDate] [datetime] NULL, [State] [int] NULL CONSTRAINT [DF_BillNo_State] DEFAULT ((0)), CONSTRAINT [PK_BillNo] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE PROCEDURE [dbo].[p_GetBillNo] --产生按年月日排列的档案号 @BillName varchar(20), @No varchar(20) output, --为产生的档案号 @NumBit int = 4 --编号位数 AS DECLARE @MaxNo int, @MaxDate varchar(20), @Prefix varchar(20), @xNumBit int, @CurrDate varchar(12), @FILL_ZERO int, @iMaxNo int SET @FILL_ZERO = 100000000 SET @CurrDate = CONVERT(VARCHAR(8), GETDATE(),112) SELECT @MaxNo = MaxNo, @MaxDate = MaxDate, @Prefix = Prefix, @xNumBit = NumBit FROM BillNo WITH(XLOCK) WHERE BillName = @BillName IF @@ROWCOUNT <> 1 RETURN -1 IF @xNumBit <> 0 SET @NumBit = @xNumBit IF @MaxDate <> @CurrDate OR @MaxDate IS NULL OR @MaxNo IS NULL SET @iMaxNo = 1 ELSE SET @iMaxNo = @MaxNo + 1 UPDATE BillNo SET MaxNo = @iMaxNo, MaxDate = @CurrDate, EditDate = GetDate() WHERE BillName = @BillName IF @@ROWCOUNT <> 1 RETURN -1 SET @No = COALESCE(@Prefix, '') + @CurrDate + RIGHT (CAST(@FILL_ZERO + @iMaxNo AS VARCHAR), @NumBit) RETURN 1
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有