MSSQL 生成編號 --整理帖

-->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_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
*/