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
GRAVI - NON FATALI
NON GRAVI - DI AVVERTIMENTO
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:
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:
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:
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:
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
RAISERROR (
{ msg_id | msg_str } {, severity, state }
[, argument [,...n ] ]
)
[ WITH option [,...n ] ]
Dove:
- @msg_id
- @msg_str
- @argument
- WITH 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:
16, 1,
'Parametro 1', 45
)
Per usare RAISERROR master.dbo.sysmessages
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:
Proviamo ad eseguire la stored procedure, in modo da generare un errore:
Il messaggio restituito sarà:
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:
[ @severity = ] severity,
[ @msgtext = ] 'msg'
[, [ @lang = ] 'language' ]
[, [ @with_log = ] 'with_log' ]
[, [ @replace = ] 'replace' ]
Dove:
- @msg_id
- @severity
- @msgtext
- @lang
- @with_log
- @replace
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
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.
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
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
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:
Successivamente dopo passiamo a quello in lingua inglese (il default):
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.
