CREATE TABLE [dbo].[tbInvoice](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Company] [nvarchar](3) NOT NULL,
[Year] [nchar](4) NOT NULL,
[InvoiceNumber] [int] NOT NULL,
CONSTRAINT [PK_tbInvoice] PRIMARY KEY CLUSTERED 
(
[ID] ASC,
[Company] ASC,
[Year] ASC,
[InvoiceNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]


create Procedure sp_CreateNextInvoiceNr

AS

BEGIN 
SET XACT_ABORT ON
DECLARE @nextNumReg int
DECLARE @YEAR VARCHAR(4)

SET @YEAR = (SELECT CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)))
BEGIN TRAN

INSERT INTO [tbInvoice]
([Company]
,[Year]
,[InvoiceNumber])
VALUES
('001'
,@YEAR
,0)

SET @nextNumReg = (SELECT [InvoiceNumber] + 1
FROM [tbInvoice] 
WHERE [Year] = DATEPART(YEAR,GETDATE()) AND ID = (SELECT SCOPE_IDENTITY() AS id ) - 1)
IF(@nextNumReg =0 OR @nextNumReg ='' OR @nextNumReg IS NULL)
BEGIN

SET @nextNumReg =1
END
UPDATE [tbInvoice] 
SET [InvoiceNumber] = @nextNumReg 
where ID =(SELECT SCOPE_IDENTITY() AS id )

COMMIT TRAN
END