MSSQL 生成編號 --整理帖

 

if object_id('tb','U')is not null drop table tb

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

drop function [dbo].[f_GetBH]

GO

create function f_GetBH()

returns varchar(8)

as

  begin

     return(select right(1000001+isnull(right(max(BH),6),0),6) from tb with (xlock,paglock) )

  end

go

--建表

create table tb(BH varchar(8) primary key default dbo.f_GetBH(),ID int)

insert tb(ID) select 1

insert tb(ID) select 2

insert tb(ID) select 3

delete tb where id=2

insert tb(ID) select 4

select * from tb

/*

BH       ID

-------- -----------

000001   1

000003   3

000004   4

 

*/

 

-->Title:得到日期编号的函数

-->Author:wufeng4552

-->Date :2009-10-20 

if object_id('tb','U')is not null drop table tb

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

drop function [dbo].[f_GetRQBH]

GO

create function f_GetRQBH()

returns varchar(10)

as

  begin

     declare @dt varchar(6)

     select @dt=convert(varchar(6),getdate(),12)

     return

    (select @dt+right(1000001+isnull(right(max(BH),3),0),3) from tb with (xlock,paglock) where bh like @dt +'%')

  end

go

--建表

create table tb(BH varchar(10) primary key default dbo.f_GetRQBH(),ID int)

insert tb(ID) select 1

insert tb(ID) select 2

insert tb(ID) select 3

delete tb where id=2

insert tb(ID) select 4

select * from tb

/*

BH         ID

---------- -----------

091020001  1

091020003  3

091020004  4

*/

-->Title:使用编号表生成流水号

-->Author:wufeng4552

-->Date :2009-10-20 

IF OBJECT_ID('TB_NO','U')IS NOT NULL DROP TABLE TB_NO

GO

CREATE TABLE tb_NO(

Name char(2) PRIMARY KEY,               --编号种类的名称

Head nvarchar(10) NOT NULL DEFAULT '',  --编号的前缀

CurrentNo int NOT NULL DEFAULT 0,       --当前编号

BHLen int NOT NULL DEFAULT 6,           --编号数字部分长度

DESCRIPTION NVARCHAR(50))               --编号种类说明

INSERT tb_NO SELECT 'CG','CG',0,4,N'采购订单'

UNION  ALL   SELECT 'CJ','CJ',0,4,N'采购进货'

UNION  ALL   SELECT 'JC','JC',0,4,N'进仓单'

UNION  ALL   SELECT 'ZC','ZC',0,4,N'转仓单'

UNION  ALL   SELECT 'CC','CC',0,4,N'出仓单'

GO

IF OBJECT_ID('P_NEXTBH','P')IS NOT NULL DROP PROC P_NEXTBH

GO

CREATE PROC p_NextBH

@Name char(2),           --编号种类

@BH nvarchar(20) OUTPUT  --新编号

AS

BEGIN TRAN

    UPDATE tb_NO WITH(ROWLOCK) SET 

        @BH=Head+RIGHT(POWER(10,BHLen)+CurrentNo+1,BHLen),

        CurrentNo=CurrentNo+1

    WHERE Name=@Name

COMMIT TRAN

GO

--获取 CJ 的新编号

DECLARE @bh char(6)

EXEC p_NextBH 'CJ',@bh OUT

SELECT @bh

/*

------

CJ0001

*/