How to obtain the serial number in sql

This article is about how to get the serial number in sql. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

It is often used to generate order numbers and other scenarios. sqlserver implements 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)

Obtain serial number, through storage process:

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

Testing:

declare @ret int exec GetSeq 'Contract', 2017,10,@ret print @ret Thank you for reading! About "sql serial number how to get" this article is shared here, I hope the above content can have some help for everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!