冰雪

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

2020-01-16 13:49:34来源:励志吧0次阅读

示例:

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

脑梗塞老吐
小孩脾胃虚弱如何调理
止咳药不含防腐剂效果咋样
糖尿病胃轻瘫便秘的症状
分享到: