Introdurre

La gestione degli errori in SQL Server è ora più semplice grazie a varie tecniche.

SQL Server ha introdotto opzioni per aiutare gli utenti a controllare gli errori in modo più efficace

Di solito, l'utente finale non sarà in grado di rilevare l'errore che appare sul lato dell'applicazione.

Pertanto, SQL Server fornisce un'istruzione TRY… CATCH per un controllo degli errori più efficiente nel back-end.

Alcune funzioni di sistema possono stampare informazioni rilevanti sull'errore, semplificando la correzione dell'errore.

Tipi di errori

I programmatori T-SQL dovranno essere a conoscenza dei tipi di errori che si verificano quando lavorano con le istruzioni di SQL Server. Il primo passo è identificare il tipo di errore, quindi capire come gestirlo.

Elenco dei tipi di errore

Errori di sintassi

Si verifica un errore di sintassi quando il codice non è sintatticamente corretto, SQL Server non è in grado di capire. Questi errori vengono rilevati da SQL Server prima di avviare l'esecuzione del blocco T-SQL o della stored procedure.

Quando l'utente digita le parole chiave o le operazioni sbagliate perché non ricorda la sintassi valida, l'editor di codice lo indicherà.

Se l'utente dimentica di digitare una parola chiave o è necessaria un'azione per completare il comando, l'editor di codice segnalerà un errore quando l'utente esegue il comando.

Gli errori di sintassi (errori di sintassi) saranno facilmente identificabili come indicherà l'editor di codice, tuttavia, se si utilizza un'applicazione a riga di comando, l'errore verrà visualizzato solo dopo che l'utente ha eseguito il comando.

Errori di runtime

Si verifica un errore di runtime quando un'applicazione tenta di elaborare un'attività che non è supportata da SQL Server o dal sistema operativo.

Alcuni errori di runtime che possono essere riscontrati sono i seguenti:

• Gestire il calcolo della divisione per 0
• Tenta di eseguire codice che non è definito in modo esplicito. (L'esempio seguente presenta una situazione in cui il valore è maggiore di quanto il tipo di dati possa rappresentare).

Situazioni comuni in cui si verificano errori di runtime:

• Utilizzare stored procedure, funzioni o trigger non disponibili (eliminati).
• Tentativo di elaborare un'attività che opera su un oggetto o una variabile che non può essere elaborata (tabella eliminata, non nel database…)
• La situazione di overflow della ram (overflow dello stack) quando l'applicazione è sovraccaricata
• Prova a gestire attività con tipi di dati incompatibili.
• Uso improprio delle affermazioni condizionali.

Implementare la gestione degli errori

Nel processo di sviluppo del software, una delle cose importanti è prestare attenzione alla gestione degli errori. In un certo senso, gli utenti devono occuparsi della gestione delle eccezioni durante la progettazione del database. Possono essere utilizzati diversi meccanismi di elaborazione.

• Quando si eseguono istruzioni DML come INSERT, DELETE, UPDATE, gli utenti possono gestire gli errori per garantire un output corretto.
• Quando la transazione non riesce e l'utente deve annullare la transazione, dovrebbe essere visualizzato un messaggio di errore appropriato per l'utente.
• Quando si utilizzano i cursori in SQL Server, gli utenti possono gestire gli errori per garantire risultati accurati.

TRY … CATCH blocco comandi

Il blocco TRY … CATCH viene utilizzato per implementare la gestione delle eccezioni in Transact SQL. Una o più istruzioni T-SQl sono delimitate da un blocco TR. Se si verifica un errore in un blocco TRY, il controllo passa a un blocco catch, che può contenere una o più istruzioni all'interno del blocco CATCH.

Sintassi:

 BEGIN TRY {sql_statement | statement_block} END TRY BEGIN CATCH [ {sql_statement | statement_block}] END CATCH [;]

Per esempio:

 BEGIN TRY DECLARE @num int; SELECT @num=217/0; END TRY BEGIN CATCH PRINT 'Error occurred, unable to devide by 0' END CATCH;

Informazioni sull'errore

La soluzione migliore è visualizzare le informazioni sull'errore insieme all'errore, in modo che possa aiutare a risolvere l'errore in modo rapido ed efficiente.

Per fare ciò, le funzioni di sistema devono essere utilizzate all'interno del blocco CATCH per trovare informazioni sull'errore generato durante l'esecuzione del blocco.

Elenco delle funzioni di sistema che forniscono informazioni sugli errori:

• ERROR_NUMBER() : restituisce il numero di errore.
• ERROR_SERVERITY() : restituisce la gravità
• ERROR_STATE(): restituisce il numero dello stato di errore.
• ERROR_PROCEDURE(): restituisce il nome del trigger o della procedura memorizzata che ha causato l'errore.
• ERROR_LINE(): restituisce il numero di righe che hanno causato l'errore
• ERROR_MESSAGE(): restituisce il testo completo dell'errore, il testo conterrà i valori forniti come parametri come nomi oggetto, lunghezza.

Queste funzioni restituiranno NULL se chiamate al di fuori del blocco CATCH

Usa TRY…CATCH con informazioni sull'errore

 USE AdventureWorks2019; GO BEGIN TRY SELECT 217/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumer, ERROR_SEVERITY() AS ErrorSeverity, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH; GO

La procedura memorizzata di esempio contiene funzioni di gestione degli errori:

 USE AdventureWorks2019; GO IF OBJECT_ID ('sp_ErrorInfo','P') IS NOT NULL DROP PROCEDURE sp_ErrorInfo; GO CREATE PROCEDURE sp_ErrorInfo AS SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; GO BEGIN TRY SELECT 217/0 END TRY BEGIN CATCH EXEC sp_ErrorInfo; END CATCH

Utilizzo di PROVA… CATTURA nella transazione

 USE AdventureWorks2019; GO BEGIN TRANSACTION; BEGIN TRY DELETE FROM Production.Product WHERE ProductID = 980; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GO

Transazione non vincolante

Se viene generato un errore dal blocco TRY, questo ha effetto e la transazione viene considerata come una transazione non vincolata. Una transazione non comitable esegue solo una TRANSAZIONE ROLLBACK o un'operazione di lettura.

La transazione non eseguirà alcuna istruzione Transact-SQL che esegue commit di transazione o operazioni di scrittura.

@@ERRORE

La funzione @@ERROR restituisce il numero di errore dell'ultima istruzione T-SQL eseguita.

Sintassi:

 @@ERROR

Per esempio:

 USE AdventureWorks2019; GO BEGIN TRY UPDATE HumanResources.EmployeePayHistory SET PayFrequency = 4 WHERE BusinessEntityID = 1; END TRY BEGIN CATCH IF @@ERROR = 547 PRINT N'Check constraint violation has occurred.'; END CATCH

RAISERRORE

L'istruzione RAISERROR avvia la gestione degli errori per una sessione e visualizza il messaggio di errore.

RAISERROR può fare riferimento a messaggi definiti dall'utente archiviati nella vista del catalogo sys.message o creare un messaggio di errore dinamico in fase di esecuzione.

Sintassi:

 RAISERROR ( {msg_id | msg_str | @local_variable} {,serverity , state} [,argument,[,...n]]) [WITH option [,...n]]

Lì dentro:

• msg_id : specifica i messaggi di errore definiti dall'utente archiviati nella vista del catalogo sys.messages utilizzando sp_addmessage.
• msg_str : definisce i messaggi di errore definiti dall'utente con la formattazione. msg_str è una stringa di caratteri con opzioni di conversione. I parametri possono essere d,i,o,s,x,X,u simili al formato stringa nella funzione printf.

Elenco dei valori per le opzioni personalizzate:

I seguenti errori verranno restituiti se RAISERROR viene eseguito:

• Al di fuori dell'ambito del blocco TRY
• Ha una gravità di 10 o meno nel blocco TRY
• Gravità 20 o superiore, disconnessione del database.

Esempio utilizzando RAISERROR() per stampare un errore personalizzato:

 RAISERROR (N'This is an error message %s %d',10,1,N'serial number',23); GO

Esempio utilizzando RAISERROR in TRY CATCH:

 BEGIN TRY RAISERROR('Raises Error in the TRY block',16,1); END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE() , @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState); END CATCH;

ERRORE_GRAVITÀ

La funzione ERROR_SEVERITY restituisce la gravità dell'errore nel blocco catch della struttura TRY CATCH.

Questa funzione restituirà null se chiamata al di fuori del blocco CATCH. ERROR _SEVERITY può essere richiamato ovunque nell'ambito del blocco CATCH.

In un blocco CATCH nidificato, ERROR_SEVERITY restituirà un errore determinando l'ambito del blocco catch a cui fa riferimento.

Sintassi:

 ERROR_SEVERITY()

Per esempio:

 BEGIN TRY SELECT 217/0; END TRY BEGIN CATCH SELECT ERROR_SEVERITY() AS ErrorSeverity; END CATCH; GO

ERRORE_PROCEDURA

La funzione ERROR_PROCEDURE restituisce il nome del trigger o della procedura memorizzata in cui è stato eseguito l'errore di un costrutto TRY CATCH.

Sintassi:

 ERROR_PROCEDURE()

Restituisce il tipo di dati nvarchar. Quando la funzione viene chiamata nel blocco CATCH, restituisce il nome della procedura memorizzata in cui si è verificato l'errore.

ERROR_PROCEDURE può essere chiamato ovunque all'interno del blocco CATCH.

Per esempio:

 USE AdventureWorks2019; GO IF OBJECT_ID ('usp_Example','P') IS NOT NULL DROP PROCEDURE usp_Example; GO CREATE PROCEDURE usp_Example AS SELECT 217/0; GO BEGIN TRY EXECUTE usp_Example; END TRY BEGIN CATCH SELECT ERROR_PROCEDURE() AS ErrorProcedure; END CATCH; GO

ERROR_NUMBER

La funzione ERROR_NUMBER richiamata nel blocco CATCH restituisce il numero di errori generati dal blocco CATCH quando viene eseguita la struttura TRY >> CATCH.

Sintassi:

 ERROR_NUMBER()

ERROR_NUMBER restituisce il codice di errore corrispondente indipendentemente dal numero di esecuzioni di errore (in caso di più eccezioni in diverse istruzioni) ed è eseguito nell'ambito del blocco CATCH. Questo è diverso da @@ERROR perché restituisce solo il numero di errore interno immediatamente dopo un errore e l'ambito è solo sulla prima istruzione nel blocco CATCH.

Per esempio:

 BEGIN TRY SELECT 217/0; SELECT 218/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH; GO

MESSAGGIO DI ERRORE

La funzione ERROR_MESSAGE restituisce il messaggio di testo dell'errore causato nella struttura nella struttura TRY CATCH.

Sintassi:

 ERROR_LINE()

Quando questa funzione viene chiamata in un blocco CATCH, restituisce il testo completo del messaggio di errore che ha causato l'esecuzione del blocco CATCH.

Il testo include i valori forniti per qualsiasi parametro che può essere sostituito come il nome dell'oggetto, l'ora o la lunghezza.

Per esempio:

 BEGIN TRY SELECT 200/0; END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH; GO

ERRORE_LINE

La funzione ERROR_LINE restituisce il numero di righe con errori nel blocco TRY CATCH.

Sintassi:

 ERROR_LINE()

Quando la funzione viene chiamata nel blocco CATCH, restituisce il numero di righe che hanno riscontrato l'errore. Se si verifica un errore all'interno di un trigger o di una procedura memorizzata, restituisce il numero di righe in quel trigger o nella procedura memorizzata. Simile ad altre funzioni, questa funzione restituisce NULL se viene chiamata al di fuori dell'ambito del blocco CATCH.

Per esempio:

 BEGIN TRY SELECT 217/0; END TRY BEGIN CATCH SELECT ERROR_LINE() AS ErrorLine; END CATCH; GO

Errori che non sono interessati dal costrutto TRY CATCH

Il blocco TRY CATCH non rileverà le seguenti condizioni:

• Le informazioni del messaggio o dell'avviso hanno gravità 10 o inferiore.
• Un errore di gravità 20 o superiore interrompe l'attività di esecuzione di Motore di database di SQL ServerSQL Server per la sessione.
• Se l'errore riscontrato ha una gravità pari o superiore a 20 e la connessione al database non viene interrotta, TRY CATCH gestirà l'errore.
• Avviso come una connessione client interrotta o richieste interrotte.
• Quando la sessione termina con l'istruzione KILL, tramite l'amministratore di sistema.

I seguenti tipi di errore non gestiti da un blocco CATCH si verificano allo stesso livello di esecuzione di quello del costrutto TRY CATCH:

• Errore di compilazione come errore legale.
• Gli errori che si verificano durante la compilazione a livello di istruzione, ad esempio errori di risoluzione del nome oggetto (il nome oggetto non esiste) si verificano dopo la compilazione a causa della risoluzione ritardata del nome.

Per esempio:

 USE AdventureWorks2109; GO BEGIN TRY SELECT * FROM Nonexistent; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCH

GETTARE

L'istruzione THROW solleva un'eccezione e trasferisce il controllo dell'esecuzione a un blocco CATCH di una struttura TRY…CATCH.

Sintassi:

 THROW [{error_number | @local_varibale}, {message | @local_variable}, {state | @local_variable}] [;]

Per esempio:

 USE tempdb; GO CREATE TABLE dbo.TestRethrow (ID INT PRIMARY KEY); BEGIN TRY INSERT dbo.TestRethrow(ID) VALUES(1); INSERT dbo.TestRethrow(ID) VALUES(1); END TRY BEGIN CATCH PRINT 'In catch block.'; THROW END CATCH;

Quando si utilizza THROW, anche se il comando non riesce a eseguire il binding, viene ancora stampata l'istruzione PRINT all'interno del blocco CATCH, che è il ruolo di THROW in SQL ServerSQL Server.