sql serial number acquisition code instance

This article mainly introduces the sql serial number to obtain the code example, in this article through the example code introduction is very detailed, to everybody's study or the work has 1 fixed reference study value, the need friend may refer to

It is often used to generate serial numbers such as order numbers. sqlserver realizes serial numbers, as follows:

Table tb_b_Seq (serial number table):

CREATE TABLE tb_b_Seq( 
Year int ,-- Year  
Month int,-- Month  
Attr varchar(50),-- Attribute  
Seq int-- Serial number  
) 

Gets the serial number, using the stored procedure:

create Proc GetSeq ( 
@attr varchar(50),
@year int, 
@month int, 
@Return int output ) 
As 
set @Return=(select top 1 Seq from tb_b_Seq where Attr=@attr and MONTH=@month and YEAR=@year) 
if(@Return is null) 
begin 
 set @Return=1 
 insert into FMDS_tb_b_Seq (Attr,Year,Month,Seq) values (@attr,@year,@month,@Return) 
end 
else 
begin 
 set @Return=@Return+1 
 update FMDS_tb_b_Seq set Seq=@Return where Attr=@attr and MONTH=@month and YEAR=@year 
end

Test:

declare @ret int 
exec GetSeq 'Contract',2017,10,@ret 
print @ret