求2个sql的自动编号语句或者存储过程
一条     是可以自动依据日期产生自动编号的,比如说前面要加个固定的字母BN2007050001,插入前要先检查一下数据库里有没有重复的,这个自动编号的数字是要和其他录入的数据一起写到表里的

第二条   也差求不多:

从一固定的格式开始增长比如说591-700-001   如果001后几位满了999的话则进位到700那里   700满了的话就进位到591那里.都是要先检查表里有没有重复的.

完了   问题简单,但是做不来,请原谅啊.

------解决方案--------------------
---第二条 , 可以把varchar变成int然后处理,在转成varchar 就是你要的结果了,下面是个例子

select cast(cast(left( '591-699-999 ',3)+ '000000 ' as int)+cast(substring( '591-699-999 ',5,3)+ '000 ' as int)+cast(right( '591-699-999 ',3) as int)+1 as varchar(15))
------解决方案--------------------
if not exists(select 1 from table1 where DateField= 'BN '+replace (Convert(nvarchar(10),getdate(),120), '- ', ' '))
BEGIN

INSERT INTO table1 (DateField, FIELD) VALUES (getdate(), 'BN '+replace (Convert(nvarchar(10),getdate(),120), '- ', ' '))
END




if not exists(select 1 from table1 where Field= stuff(stuff(REPLACE( '591-700-001 ', '- ', ' ')+1,4,0, '- '),8,0, '- ') )
BEGIN

INSERT INTO table1 ( FIELD) VALUES ( stuff(stuff(REPLACE( '591-700-001 ', '- ', ' ')+1,4,0, '- '),8,0, '- '))
END

------解决方案--------------------
---上面的语句升级版
create table b(No varchar(15))
insert b select '591-699-999 '

insert b
select stuff(stuff(cast(left(max(No),3)+ '000000 ' as int)+cast(substring(max(No),5,3)+ '000 ' as int)+cast(right(max(No),3) as int)+1,4,0, '- '),8,0, '- ')
from b

select * from b

delete from b

------解决方案--------------------
1.
create view v_getdate
as
select dt=convert(char(6),getdate(),112)
go
create function f_nextBH()
returns char(12)
as
begin
declare @dt char(6)
select @dt=dt from v_getdate
return(
select 'BN '+@dt+right(10001+isnull(rgight(max(bhcol),4),0),4) from tb with(xlock,paglock)
)
end
2.
declare @bh char(11)
declare @bh3 char(3)
declare @bh2 char(3)
declare @bh1 char(3)
declare @rtn varchar(11)
set @bh= '591-700-001 '
select @bh3=right(@bh,3)
select @bh2=substring(@bh,5,3)
select @bh1=left(@bh,3)
if @bh3 <> '999 '
begin
select @rtn= '- '+right(1001+isnull(right(max(bhcol),3),0),3) from tb where bhcol like @bh1+ '- '+@bh2+ '% '
set @rtn=@bh1+ '- '+@bh2+@rtn
end
else
if @bh2 <> '999 '
begin
select @rtn= '- '+right(1001+isnull(substring(max(bhcol),5,3),0),3) from tb where bhcol like @bh1
set @rtn=@bh1+@rtn+ '999 '
end
else
if @bh3 <> '999 '
begin
select @rtn=right(1001+isnull(left(max(bhcol),3),0),3) from tb where bhcol like '%-999-999 '
set @rtn=@rtn+ '-999-999 '
end
else
set @rtn= 'a '