salve Vincenzo,
in caso di failure, lo slot di [InvoiceNumber] sara' disponibile per il prossimo richiedente, mentre [ID] no, e restera' valorizzato come da ultimo inserimento fallito, quindi avrai dei gap, anche se cio' ovviamente e' irrilevante in quanto l'unica nunerazione progressiva senza gap deve essere [InvoiceNumber] ...
al di la' di cio' non comprendo tutta la logica...
INSERT INTO [tbInvoice]
([Company]
,[Year]
,[InvoiceNumber])
VALUES
('001'
,@YEAR
,0)e' sbagliato in quanto: 1) Id e' un numero nel dominio degli interi e tu gli vuoi assegnare '001'.. 2) Id, oltre ad essere un intero, ha anche impostata la proprieta' identity, e quindi non devi (e non puoi direttamente, a meno di non impostare SET IDENTITY INSERT {ON|OFF}) fornirgli un valore in quanto questo sara' gestito e generato direttamente dallo storage engine al momento del tentativo di inserimento...
SET @nextNumReg = (SELECT [InvoiceNumber] + 1
FROM [tbInvoice]
WHERE [Year] = DATEPART(YEAR,GETDATE()) AND ID = (SELECT SCOPE_IDENTITY() AS id ) - 1)
non comprendo il senso dell'istruzione... recuperi il numero [InvoiceNumber] incrementadolo di 1 filtrando per Anno (giusto) e Id = riga appena inserita?
io non agirei cosi', e recupererei dinamicamente l'informazione al momento dell'inserimento, qualche cosa simile a
SET NOCOUNT ON;
USE tempdb;
GO
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];
GO
DECLARE @Company nvarchar(3) = 'abc';
INSERT INTO dbo.tbInvoice ([Company], [Year], [InvoiceNumber])
SELECT @Company, CONVERT(varchar(4),DATEPART(YEAR, GETDATE()))
, ISNULL(MAX(base.InvoiceNumber), 0) + 1
FROM dbo.tbInvoice AS base
WHERE [Year] = CONVERT(varchar(4),DATEPART(YEAR, GETDATE()));
GO
DECLARE @Company nvarchar(3) = 'abc';
INSERT INTO dbo.tbInvoice ([Company], [Year], [InvoiceNumber])
SELECT @Company, CONVERT(varchar(4),DATEPART(YEAR, GETDATE()))
, ISNULL(MAX(base.InvoiceNumber), 0) + 1
FROM dbo.tbInvoice AS base
WHERE [Year] = CONVERT(varchar(4),DATEPART(YEAR, GETDATE()));
GO
PRINT 'questa fallisce';
DECLARE @Company nvarchar(3) = 'abc';
INSERT INTO dbo.tbInvoice ([Company], [Year], [InvoiceNumber])
SELECT @Company + 'x', CONVERT(varchar(4),DATEPART(YEAR, GETDATE()))
, ISNULL(MAX(base.InvoiceNumber), 0) + 1
FROM dbo.tbInvoice AS base
WHERE [Year] = CONVERT(varchar(4),DATEPART(YEAR, GETDATE()));
GO
DECLARE @Company nvarchar(3) = 'abc';
INSERT INTO dbo.tbInvoice ([Company], [Year], [InvoiceNumber])
SELECT @Company, CONVERT(varchar(4),DATEPART(YEAR, GETDATE()))
, ISNULL(MAX(base.InvoiceNumber), 0) + 1
FROM dbo.tbInvoice AS base
WHERE [Year] = CONVERT(varchar(4),DATEPART(YEAR, GETDATE()));
GO
SELECT * FROM dbo.tbInvoice
GO
DROP TABLE dbo.tbInvoice;
--<-------
questa fallisce
Msg 8152, Level 16, State 13, Line 4
String or binary data would be truncated.
The statement has been terminated.
ID Company Year InvoiceNumber
----------- ------- ---- -------------
1 abc 2014 1
2 abc 2014 2
4 abc 2014 3
come vedi il terzo inserimento fallisce (al di la' del motivo), ma il gap nella numerazione di InvoiceNumber non c'e', mentre ovviamente c'e' nella colonna Id
ovviamente, visto che dopo l'inserimento in dbo.tbInvoice dovrai aggiungere righe in almeno altre 2 tabelle, racchiudi il tutto in una transazione esplicita, che ti consente di garantire adeguata protezione all'operazione ACID
perche' la colonna [Year] e' nchar? sicuramente non puo' essere un valore con carattere diverso da numeri, e quindi perche' non usare un intero con dominio vincolato da un costraint (BETWEEN 1999 AND 2050)... occupi "spazio" per niente ed il dominio dell'attributo e' sbagliato...
la chiave primaria, visto che hai utilizzato una chiave surrogata univoca basata su Identity (la colonna Id), la baserei eventualmente su quella, mentre farei un'altro indice su [Year] + [InvoiceNumber]...
la colonna [Company] nella chiave non ha tecnicamente alcun senso se non nel caso di tabella/database multiazienda e [Company] stia ad identificare un'azienda specifica... in tal caso farei la chiave primaria su [Company] + [Year] + [InvoiceNumber] e toglierei completamente l'attributo [Id]... l'unico motivo per mantenerlo e' per farci una chiave primaria clusterizzata visto che clusterizzare sul set di colonne [Company] + [Year] + [InvoiceNumber] sicuramente causerebbe elevata frammentazione
saluti
