建立表
CREATE TABLE [dbo].[Tab] (
[ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[IDName] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
建立存储过程
ID 自动添加 yymmdd+四位序列
如:0601110001,06010110002,0601220001...
Create procedure sp_CreateID
@IDName char(10),
@Ret_err int=0 output
as
declare @ID char(10)
Select top 1 @ID =ID from tab
where left(ID,6)= replace(convert(char(10),getdate(),2),'.','')
order by ID desc
if (@ID is not null)
begin
set @ID = replace(convert(char(10),getdate(),2),'.','')+right(10001+@ID,4)
select @Ret_err=0
end
else
begin
set @ID =replace(convert(char(10),getdate(),2),'.','')+'0001'
select @Ret_err=1
end
insert into tab(ID,IDName) values(@ID,@IDName)
go
测试:
declare @Ret as int
exec sp_CreateID '姓名' ,@Ret output
print @Ret