SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[nsc_serial_number](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[name] [varchar](255) NULL,
[year] [int] NOT NULL,
[month] [int] NOT NULL,
[day] [int] NOT NULL,
[number] [bigint] NOT NULL,
CONSTRAINT [PK_nsc_serial_number] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_nsc_serial_number] UNIQUE NONCLUSTERED
(
[day] ASC,
[month] ASC,
[year] ASC,
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
创建存储过程
CREATE PROCEDURE [dbo].[nsc_get_next_serial_number](@name varchar(255),@year int,@month int,@day int)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
declare @oldnumber bigint,
@newnumber bigint,
@id bigint
p:
select @oldnumber = number,@id=id from nsc_serial_number where name=@name and year=@year and month=@month and day=@day;
if(@oldnumber is null)
begin
begin try
insert into nsc_serial_number (name,year,month,day,number) values (@name,@year,@month,@day,0);
set @oldnumber=0;
end try begin catch
goto p;
end catch
end
set @newnumber=@oldnumber+1;
update nsc_serial_number set number = @newnumber where id=@id and number=@oldnumber;
if(@@rowcount=0)
begin
goto p;
end
return @newnumber;
END