CREATE TABLE [dbo].[tbInvoice](
[Company] [nvarchar](3) NOT NULL,
[Year] [nchar](4) NOT NULL,
[InvoiceNumber] [int] NOT NULL,
CONSTRAINT [PK_tbInvoice] PRIMARY KEY CLUSTERED 
(
[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]

declare @OKInvoiceNumber as bit,@NextInvoiceNumber as int, @CYCLE AS INT,
        @MAXCYCLE AS INT

set @OKInvoiceNumber = 0
set @NextInvoiceNumber = 0  
SET @CYCLE = 0
SET @MAXCYCLE = 2

BEGIN TRAN
WHILE @OKInvoiceNumber = 0
BEGIN
set @NextInvoiceNumber = (select MAX(InvoiceNumber) from tbInvoice where [Year]='2013' and Company= '001') + 1
IF(select COUNT(*) from tbInvoice where [Year]=2013 and Company= '001' and InvoiceNumber=@NextInvoiceNumber ) = 0
  BEGIN
  INSERT INTO 
           ([Company]
           ,[Year]
           ,[InvoiceNr])
     VALUES
           ('001'
           ,'2013'
           ,@NextInvoiceNumber)
  IF @@ERROR <>0
    BEGIN
    ROLLBACK TRAN
       IF @CYCLE = @MAXCYCLE
      BEGIN
        SET @OKInvoiceNumber = -1
        BREAK        
      END
      ELSE 
      BEGIN
      SET @CYCLE = @CYCLE + 1
      END
    END
    ELSE
    BEGIN 
    SET @OKInvoiceNumber =1
    END
  END
  ELSE
  BEGIN 
  IF @CYCLE = @MAXCYCLE
      BEGIN
        SET @OKInvoiceNumber = -1
        BREAK        
      END
  ELSE 
      BEGIN
      SET @CYCLE = @CYCLE + 1
      END
  END  
  END
END

-- OTTENUTO IL @NextInvoiceNumber EFFETTUO LE ALTRE INSERT/UPDATE SEMPRE ALL'INTERNO DELLA TRAN
-- SE QUALCOSA FA SCATTARE IL rollback si riazzera anche tbInvoice
COMMIT TRAN