--根据给定的编码比如Emp,生成一个字符串类型的流水号,如:20110102Emp0001
--建立表
CREATE TABLE PrimKey(
cDate datetime not null,
cChar varchar (3) not null,
cCount int not null
)
--创建主键的存储过程
Create Proc GetPrimKey
@char varchar(3),
@res nvarchar(15) output
As
Begin
declare @count int
begin try
select @count=isnull(cCount,0) from PrimKey where convert(varchar(8),cDate,112)=Convert(nvarchar(8),getdate(),112) and cChar=@char
set @count=isnull(@count,0);
if (@count>0)
begin
Update PrimKey set cCount=@count+1 where convert(varchar(8),cDate,112)=Convert(nvarchar(8),getdate(),112) and cChar=@char
end
if(@count<=0)
begin
Insert into PrimKey values(Convert(nvarchar(10),getdate(),21),@char,1);
end
set @res =Convert(nvarchar(8),getdate(),112)+@char+right('00000000'+convert(varchar(5),@count+1),4)
end try
begin catch
set @res='One Error';
end catch
End
--测试调用
declare @res nvarchar(20)
exec GetPrimKey 'Emp',@res output
print @res
--Create by Ranen