How to Create Next Number for Batch Processing in SQL SERVER

 

CREATE TABLE [dbo].[TBL_BatchNN](

[NN] [int] IDENTITY(1,1) NOT NULL,

[strgetdate] [datetime] NULL,

 CONSTRAINT [PK_TBL_BatchNN] PRIMARY KEY CLUSTERED 

(

[NN] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER Proc [dbo].[SP_BatchLoadNN] AS

Set nocount on

declare @NN int 

Insert into TBL_BatchNN(dateupdate) select getdate()

select @NN=max(NN) from TBL_BatchNN

delete TBL_BatchNN

SELECT  @NN