Riuscire a gestire gli errori generati da una applicazione è uno degli obiettivo principali dello sviluppatore. SQL Server ci mette a disposizione diversi strumenti (funzioni, variabili e procedure di sistema) per svolgere questo compito ed in questo articolo li esamineremo a fondo.
Concetti introduttivi
Durante l'esecuzione di batch o istruzioni T-SQL si verificano errori di varia natura ed entità. Questi errori vengono suddivisi in 3 tipologie sulla base della loro gravità:
- FATALI
Sono errori GRAVI; qualora si verificassero SQL Server termina l'intero batch (o programma) T-SQL, interrompe la connessione con il client e notifica l'errore sia al sistema operativo tramite il log degli errori che al client stesso tramite un messaggio appositamente formattato. - NON FATALI
Sono errori NON GRAVI; qualora si verificassero SQL Server termina l'istruzione corrente ma non l'eventuale batch (o programma) T-SQL di cui l'istruzione fa parte. In questo caso non viene notificato l'errore al log del sistema operativo, ma viene comunque inviato al client un messaggio informativo riguardante l'errore. - DI AVVERTIMENTO
Sono messaggi di output che SQL Server invia al client per informarlo di particolari situazioni o in risposta a particolari comandi. Non terminano in alcun modo né il batch né l'istruzione T-SQL corrente.
SQL Server segue una logica molto semplice per la cattura degli errori. Se non verifichiamo il tipo e la gravità di un errore all'interno del flusso logico del codice T-SQL, SQL Server non si preoccupa di segnalare anomalie o mancanze. Per questo motivo il programmatore, in primis, deve valutare tutte le possibili condizioni che possano essere fonte di errore. Un esempio banale:
CREATE PROCEDURE dbo.P_TEST_ERR1
@SALARIO DECIMAL = NULL
AS
IF @SALARIO>0 BEGIN
PRINT 'OK, procedo all''inserimento'
END
ELSE BEGIN
--GESTISCO L'ERRORE CON LA FUNZIONE RAISERROR
RAISERROR('Attenzione! Il Salario deve essere maggiore di 0',16,1)
END
GO
SQL Server non si preoccupa di valutare in contenuto della variabile @SALARIO, saremo noi in ad implementare il codice T-SQL necessario per gestire la situazione: salario con valore pari o inferiore a 0, creando un messaggio di errore a hoc con RAISERROR (che analizzeremo più avanti). Quindi non aspettiamoci aiuto, in questo senso, da SQL Server.
Anatomia di un messaggio di errore
Proviamo ora ad dettagliare i messaggi di errore che SQL Server ci invia, per fare ciò eseguiamo la procedura memorizzata P_TEST_ERR1 dal Query Analyzer in modo da poter scatenare un errore:
/*Così facendo creo un messaggio di errore*/
EXEC dbo.P_TEST_ERR1 '0'
Il risultato sarà simile a quello indicato nell'immagine sottostante:
Vediamo in dettaglio le componenti dell'errore:
- Numero
Ad ogni errore corrisponde un numero ben preciso. - Livello
Indica la gravità dell'errore, è rappresentato da valori interi compresi tra 1 e 25. - Stato
Ulteriore indicazione sull'errore. SQL Server può catturare lo stesso errore (inteso come numero) in diversi punti di esecuzione, quindi per aver unicità dell'errore oltre al numero viene definito uno stato dell'errore. - Linea
Indica la linea di codice in cui si è generato l'errore. - Messaggio
Contiene le informazioni sull'errore. Questo può essere formattato con dei "placeholder" stile printf di C. - Nome della Procedura
Se l'errore viene catturato all'interno di una procedura, SQL Server invia al client il nome della procedura.
Tutti i messaggi di errore vengono salvati all'interno della tabella dbo.sysmessages del database master, in base al numero e allo stato dell'errore ed inoltre in funzione del langid. Sostanzialmente esiste all'interno di sysmessages una chiave surrogata del tipo:
UNIQUE CLUSTERED INDEX [sysmessages] ON [dbo].[sysmessages]([error],
[dlevel], [msglangid]) ON [PRIMARY]
Tutti i messaggi di errore definiti dall'utente partono dal numero 50001, per
visualizzarli possiamo eseguire la query:
SELECT TOP 100 Percent WITH TIES [Error] as [ErrorNum], Severity
,
msglangid as [LanguageCODE], [description] as Message
FROM master..sysmessages WHERE ERROR > 50000 ORDER BY [Error]
Sul mio portatile il risultato della query è il seguente:
Per una dettagliata descrizione dei livelli di severità degli errori vi consiglio di controllare sui BOL di SQL Server.
La funzione @@ERROR
Dopo il completamento di una qualsiasi istruzione T-SQL, SQL Server valorizza l'oggetto @@error. Se l'istruzione è andata a buon fine @@error restituirà 0 viceversa il codice di errore opportuno. @@error è quindi un parametro da controllare ad ogni esecuzione di codice T-SQL (ovviamente se richiesto) per poter valutare eventuali errori ed agire di conseguenza. Vediamo un esempio:
Use pubs
GO
DECLARE @DUMMY INT
BEGIN TRAN
INSERT INTO [pubs].[dbo].[publishers]([pub_id], [pub_name], [city], [state], [country])
VALUES(NULL, 'LIBERIA 1', 'MILANO', 'MI', 'ITALIA')
SET @DUMMY = 1
IF @@ERROR <> 0 BEGIN
PRINT 'ESEGUO ROLLBACK - TRANSAZIONE KO'
ROLLBACK TRAN
END
ELSE BEGIN
PRINT 'ESEGUO COMMIT - TRANSAZIONE OK'
COMMIT TRAN
END
Se eseguiamo lo script dal Query Analyzer, ci attendiamo un messaggio di errore (codice 515) da parte di SQL Server ed anche la scritta ''ESEGUO ROLLBACK - TRANSAZIONE KO', dato che stiamo tentando di inserire un valore NULL nella colonna pub_id (la chiave primaria della tabella [publishers]).
Invece no, SQL Server restituisce l'errore opportuno, ma compare la scritta 'ESEGUO COMMIT - TRANSAZIONE OK'.
Il motivo di questa anomalia è la riga SET @DUMMY = 1 sottostante all'istruzione INSERT.
Questa istruzione provvede a "svuotare" il contenuto della funzione @@error del codice di errore atteso dall'INSERT, riportando @@error a 0.
Ovviamente al momento di valutare l'errore tramite l'istruzione IF @@ERROR <> 0 BEGIN, @@error non contiene più il valore 515 ma 0 e quindi passa alla stampa della COMMIT.
Quindi attenzione: dopo l'esecuzione di una istruzione T-SQL è buona cosa memorizzare il contenuto della funzione @@error all'interno di variabile locale alfine di poter valutare in modo corretto i codici di errore anche in punti differenti del batch T-SQL.
Il codice corretto diventa:
DECLARE @DUMMY INT, @LOCAL_ERR INT
BEGIN TRAN
INSERT INTO [pubs].[dbo].[publishers]([pub_id], [pub_name], [city], [state], [country])
VALUES(NULL, 'LIBERIA 1', 'MILANO', 'MI', 'ITALIA')
/*Prima salvo l'eventuale codice di errore, 515*/
SET @LOCAL_ERR = @@ERROR
/*Ora posso eseguire anche un'altra istruzione T-SQL*/
SET @DUMMY = 1 /* Qui avviene il reset di @@ERROR che torna a 0*/
/*Valuto il codice di errore dell'istruzione INSERT per decidere se fare
il COMMIT della transazione, oppure il ROLLBACK */
IF @LOCAL_ERR <> 0 BEGIN
/*... codice...*/
Eseguendo il codice avremo un responso simile:
Possiamo anche catturare in sol colpo sia il valore di @@error che di @@rowcount:
SELECT @LOCAL_ERR = @@ERROR, @LOCAL_ROWCOUNT = @@ROWCOUNT
L'errore che abbiamo scatenato ha un livello di severità pari a 16, quindi non fatale, perciò il batch T-SQL continua anche dopo il verificarsi dell'errore. Questo è dimostrato dal fatto che vengono eseguite le istruzioni successive di ROLLBACK/COMMIT con conseguente stampa dei messaggi PRINT.
Ma se si fosse verificato un errore FATALE, cioè con un livello di severità tra 20 e 25? In questo caso l'interno batch sarebbe abortito, la connessione al client interrotta immediatamente e le istruzioni T-SQL successive all'INSERT non potrebbero essere eseguite.
Inoltre nei LOGS di SQL Server avremo delle nuove voci:
Generalmente è buona cosa lasciare a SQL Server il compito di gestire i livelli di severità più alti.
Abbiamo parlato della gestione degli errori in T-SQL e delle caratteristiche della funzione @@error. Vedremo come sia possibile personalizzare i messaggi di errore con RAISERROR ma anche come crearne di nuovi tramite la procedura di sistema SP_ADDMESSAGE.
Abbiamo già introdotto le caratteristiche più semplici della gestione degli errori in T-SQL. Ora invece esamineremo quelle più sofisticate come la creazione e la personalizzazione dei messaggi di errore tramite procedure e funzioni di sistema.
La funzione RAISERROR
Il comando RAISERROR svolge due attività:
- Genera un messaggio di errore personalizzabile dall'utente e lo restituisce al client.
- Informa SQL Server sull'errore generato (nel momento in cui viene invocata la funzione).
Con RAISERROR è possibile recuperare una voce dalla tabella master.dbo.sysmessages (vedremo in seguito come) ma anche generare un errore personalizzato in base alle informazioni di gravità e stato specificate dall'utente. Questo a sua volta, viene inviato al client, in forma testuale, come errore generato dal SQL Server. Vediamo la sintassi del comando:
RAISERROR (
{ msg_id | msg_str } {, severity, state }
[, argument [,...n ] ]
)
[ WITH option [,...n ] ]
Dove:
- @msg_id è l'identificativo del messaggio di errore definito dall'utente, per i messaggi creati ad-hoc l'id deve essere pari o superiore a 50000.
- @msg_str è il contenuto del messaggio di errore, può avere una lunghezza anche superiore a 400 caratteri.
- @argument, argomenti utilizzati per la formattazione del messaggio.
- WITH indica le opzioni abbinate al comando, i valori accettati sono: LOG, NOWAIT, SETERROR.
L'opzione WITH LOG inserisce l'errore nel log degli errori del server e nel log delle applicazioni. L'opzione WITH NOWAIT invia i messaggi direttamente al client, senza attendere.
L'opzione WITH SETERROR imposta @@ERROR sul valore del msg_id oppure su 50000, indipendentemente dal livello di gravità.
Per usare RAISERROR in modo semplice, formattando il messaggio con stringhe e numeri:
RAISERROR ('Messaggio di errore generico %s numero %d',
16, 1,
'Parametro 1', 45
)
Per usare RAISERROR recuperando uno specifico messaggio (con ID 100001) dalla tabella master.dbo.sysmessages:
RAISERROR (100001, 16, 1, @PAR_ID, @MIN_LVL, @MAX_LVL)
Gestire gli errori nelle procedure memorizzate
Ogni volta che all'interno di una stored procedure o di un triggers vogliamo generare un errore ad-hoc dobbiamo necessariamente usare RAISERROR, vediamo un tipico esempio:
Use pubs
Go
CREATE PROCEDURE dbo.P_TEST_ERROR (@TYPE VARCHAR(40), @STORE CHAR(4),@LOW SMALLINT,
@HIGH SMALLINT, @DISCOUNT NUMERIC(9,2))
AS
IF @DISCOUNT > 10 BEGIN
RAISERROR ('Lo sconto che hai inserito (%d), non può essere maggiore di 10', 10,
1, @DISCOUNT)
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO DISCOUNTS(DISCOUNTTYPE, STOR_ID, LOWQTY, HIGHQTY, DISCOUNT)
VALUES (@TYPE,@STORE,@LOW,@HIGH,@DISCOUNT)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
END
GO
Proviamo ad eseguire la stored procedure, in modo da generare un errore:
exec dbo.P_TEST_ERROR 'Sconto fedeltà', NULL, 10, 100, 12
Il messaggio restituito sarà:
Lo sconto che hai inserito (12), non può essere maggiore di 7
La procedura sp_addmessage
Questa procedura di sistema permette creare messaggi di errore personalizzati e funziona in modo molto semplice: aggiunge i messaggi all'interno della tabella master.dbo.sysmessages. Ecco la sintassi della procedura:
sp_addmessage [ @msgnum =] msg_id,
[ @severity = ] severity,
[ @msgtext = ] 'msg'
[, [ @lang = ] 'language' ]
[, [ @with_log = ] 'with_log' ]
[, [ @replace = ] 'replace' ]
Dove:
- @msg_id è l'identificativo del messaggio di errore definito dall'utente, per i messaggi creati ad-hoc. L'id deve essere superiore a 50000.
- @severity è il livello di gravità dell'errore compreso tra 1 e 25. Solo l'amministratore del sistema può creare messaggi con un livello di gravità inferiore a 18, negli altri casi (severità sopra il 18) anche gli utenti con privelegi minori possono aggiungere messaggi
- @msgtext è il contenuto del messaggio, può contenere variabili di formattazione simili alla funzione printf nel C.
- @lang è il langID del messaggio, cioè il codice della lingua con cui viene registrato il messaggio. Insieme al msg_id formano la chiave primaria della tabella master.dbo.sysmessages.
- @with_log, informa il sistema sulla necessità o meno di trascrivere l'errore all'interno dei log di Windows e di SQL Server.
- @replace, permette di rimpiazzare un messaggio di errore esistente con un nuovo testo e un nuovo livello di gravità.
Una volta creato il messaggio possiamo recuperarlo e formattarlo utilizzando la funzione RAISERROR. Per maggiori dettagli sulla stored procedure sp_addmessage (ma anche sulle stored procedures correlate sp_altermessage, sp_dropmessage) potete cliccare qui.
Personalizzazione di codici e messaggi di errore
Molto importanti sono le funzioni di formattazione, possiamo usare diversi placeholder, ma i più comuni sono:
- %s per le stringhe
- %d per i numeri interi
Ora vedremo un esempio su come sia possibile formattare un messaggio di errore con tre semplici passaggi:
- PASSO 1 . Creiamo un messaggio di errore predefinito (con codice messaggio 50010) in base alle nostre esigenze
USE master
Go
EXEC sp_addmessage
50010, /* id del messaggio */
16, /*livello di gravità*/
'(INGLESE) Si è verificato un errore in: %s', /*contenuto del messaggio*/
' us_english', /*linguaggio*/
'FALSÈ, /*scrivi su log*/
NULL /*sostituisci precedente messaggio di errore*/ - PASSO 2. Utilizziamo la funzione RAISERROR per richiamare il messaggio di errore da una stored procedure che creata ad-hoc nel database pubs.
Use Pubs
GO
CREATE PROCEDURE dbo.P_ProvaErrore
AS
SELECT TOP 10 * FROM dbo.AUTHORS
IF @@ROWCOUNT < 11 BEGIN
RAISERROR (50010,12,1,'Recupero Lista Autori')
END
GO - PASSO 3. Eseguiamo la stored procedure e vediamo il messaggio di errore restituito
Use Pubs
GO
Exec dbo.P_ProvaErrore
/*Il messaggio di error sarà il seguente */
Server: Msg 50010, Level 12, State 1, Procedure P_ProvaErrore, Line 8
(INGLESE) Si è verificato un errore in: Recupero Lista Autori
Come avrete osservato il language ID utilizzato per la creazione del messaggio l'inglese americano, cioè quello di default per SQL Server. Difatti prima di poter localizzare un messaggio in una lingua differente dall'inglese, supponiamo l'italiano, dobbiamo prima inserire il messaggio nella lingua di principale e solo dopo possiamo localizzarlo. L'esempio seguente ci mostra come creare un secondo messaggio localizzato:
Use Master
Go
EXEC sp_addmessage 50010, /* id del messaggio */
16, /*livello di gravità*/
'(ITALIANO) Si è verificato un errore in: %s', /*contenuto del messaggio*/
'italian', /*linguaggio*/
'FALSÈ, /*scrivi su log*/
NULL /*sostituisci precedente messaggio di errore*/
Ora eseguendo la query:
SELECT * FROM master.dbo.sysmessages WHERE error=50010
Vedremo la presenza di 2 righe in master.dbo.sysmessagges, una per ogni linguaggio:
Per eliminare i messaggi di errore appena creati dobbiamo usare una procedura di sistema apposita: sp_dropmessage. Come primo passo procediamo con l'eliminazione del messaggio localizzato in lingua italiana:
EXEC sp_dropmessage 50010,'italian'
Successivamente dopo passiamo a quello in lingua inglese (il default):
EXEC sp_dropmessage 50010
Conclusione
In questo articolo abbiamo approfondito la conoscenza dei meccanismi che SQL Server utilizza per gestire gli errori con T-SQL (@@ERROR, SP_ADDMESSAGE e RAISERROR). Durante la programmazione in T-SQL (scrivere stored procedure e triggers) è bene fare uso di questi strumenti alfine di migliorare il lavoro di progettazione delle applicazioni.
