--下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
    RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)

--插入资料
BEGIN TRAN
    INSERT tb(col) VALUES(1)
    INSERT tb(col) VALUES(2)
    INSERT tb(col) VALUES(3)
    DELETE tb WHERE col=3
    INSERT tb(col) VALUES(4)
    INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN

--显示结果
SELECT * FROM tb
/*--结果
BH         col 
---------------- ----------- 
BH000001  1
BH000002  2
BH000003  4
BH000004  14
--*/

------解决方案--------------------

SQL code

if OBJECT_ID('fun_test')is not null
drop function fun_test
go
create function fun_test
(@id int,@date datetime)
returns varchar
as 
begin
return 
'ADS/WT-'+case when day(@date)>=20 
          then right('00'+ltrim(month(@date)+1),2)+right('00'+ltrim(@id),2)
          else right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) end
end
go

------解决方案--------------------

SQL code

---->>>TravyLee生成测试数据
if OBJECT_ID('fun_test')is not null
drop function fun_test
go
create function fun_test
(@id int,@date datetime)
returns varchar(20)
as 
begin
return 
'ADS/WT-'+case when day(@date)>=20 
          then right('00'+ltrim(month(@date)+1),2)+right('00'+ltrim(@id),2)
          else right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) end
end
go
if OBJECT_ID('test') is not null
drop table test
go
create table test(
id int identity,
dates date
)
go
insert test(dates)
select '2012-05-19' union all
select '2012-05-20' union all
select '2012-05-21' union all
select '2012-05-22'
select dbo.Fun_test(id,dates) as BH,dates from test
/*
BH    dates
----------------------------------
ADS/WT-0501    2012-05-19
ADS/WT-0602    2012-05-20
ADS/WT-0603    2012-05-21
ADS/WT-0604    2012-05-22
*/

------解决方案--------------------

SQL code

---->>>TravyLee生成测试数据
if OBJECT_ID('fun_test')is not null
drop function fun_test
go
create function fun_test
(@id int,@date datetime)
returns varchar(20)
as 
begin
return 
'ADS/WT-'+case when day(@date)>=20 
          then ltrim(year(dateadd(mm,1,@date)))+'-'+right('00'+ltrim(month(dateadd(mm,1,@date))),2)+right('00'+ltrim(@id),2)
          else ltrim(year(@date))+'-'+right('00'+ltrim(month(@date)),2)+right('00'+ltrim(@id),2) end
end
go
if OBJECT_ID('test') is not null
drop table test
go
create table test(
id int identity,
dates date
)
go
insert test(dates)
select '2012-05-19' union all
select '2012-05-20' union all
select '2012-05-21' union all
select '2012-05-22' union all
select '2012-12-20' union all
select '2012-12-21' 

select dbo.Fun_test(id,dates) as BH,dates from test
/*
BH    dates
ADS/WT-2012-0501    2012-05-19
ADS/WT-2012-0602    2012-05-20
ADS/WT-2012-0603    2012-05-21
ADS/WT-2012-0604    2012-05-22
ADS/WT-2013-0105    2012-12-20
ADS/WT-2013-0106    2012-12-21
*/