TRY- CATCH
TRY block and you check for the errors in the CATCH block. SQL Server has a bunch of functions that will help you identify why your code failed, here is a list of the functions and what they return
ERROR_NUMBER()
returns the number of the error
ERROR_SEVERITY()
returns the severity of the error
ERROR_STATE()
returns the error state number
ERROR_PROCEDURE()
returns the name of the stored procedure or trigger where the error occurred, this will be NULL if you run an ad-hoc SQL statement
ERROR_LINE()
returns the line number inside the routine that caused the error
ERROR_MESSAGE()
returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times
Let's run an example that generates a divide by zero error, in the catch we are just doing a simple select that calls the functions mentioned before to see what they return
BEGIN TRY
-- divide-by-zero error.
SELECT 1/0
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;
END CATCH;
output
| ErrorNumber | ErrorSeverity | ErrorState | ErrorProcedure | ErrorLine | ErrorMessage |
| 8134 | 16 | 1 | NULL | 3 | Divide by zero error encountered. |
As you can see we got all that information back, that was pretty nice. Let's take it to the next step
Create the following table to store all the error information in
CREATE TABLE LogErrors (ErrorTime datetime,
ErrorNumber int,
ErrorSeverity int,
ErrorState int,
ErrorProc nvarchar(100),
ErrorLine int,
ErrorMessage nvarchar(1000))
GO
Create this stored procedure that will insert into the table we just created
CREATE PROCEDURE prInsertError
AS
INSERT LogErrors
SELECT GETDATE(),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE() ;
GO
Run these 3 queries, they will generate 3 inserts into the LogErrors tableBEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
EXEC prInsertError
END CATCH;
BEGIN TRY
SELECT convert(int,'a')
END TRY
BEGIN CATCH
EXEC prInsertError
END CATCH;
BEGIN TRY
SELECT convert(tinyint,300)
END TRY
BEGIN CATCH
EXEC prInsertError
END CATCH;
--// Error Output..
SELECT * FROM LogErrors
| ErrorTime | ErrorNumber | ErrorSeverity | ErrorState | ErrorProc | ErrorLine | ErrorMessage |
| 14-Mar-12 | 8134 | 16 | 1 | NULL | 2 | Divide by zero error encountered. |
| 14-Mar-12 | 245 | 16 | 1 | NULL | 10 | Conversion failed when converting the varchar value 'a' to data type int. |
| 14-Mar-12 | 220 | 16 | 2 | NULL | 18 | Arithmetic overflow error for data type tinyint, value = 300. |
