Example of using error handling in SQL Server

Error handling in SQL Server as a programming block:

-- Example generate the exception for divide by zero
BEGIN TRY
  
  SELECT  1 / 0 AS Error;
END TRY
BEGIN CATCH
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_STATE() AS ErrorState,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

Example of using exceptional handling in Procedure:

--Create a table for error handling in SQL Server which will record the error:

CREATE TABLE Error_handling
(         UserName       VARCHAR(100),
          ErrorNumber    INT,
          ErrorState     INT,
          ErrorSeverity  INT,
          ErrorLine      INT,
          ErrorProcedure VARCHAR(500),
          ErrorMessage   VARCHAR(1000),
          ErrorDateTime  DATETIME,
          ErrorData      VARCHAR(MAX)
);

--Create procedure to test the exceptional handling for out of range datetime conversion error:


create procedure testpn 
as 
Begin

Begin try
select cast('10101010' as datetime);
end try

Begin catch
INSERT INTO dbo.error_handling
 VALUES (SUSER_SNAME(),ERROR_NUMBER(),ERROR_STATE(),
ERROR_SEVERITY(),ERROR_LINE(),ERROR_PROCEDURE(),ERROR_MESSAGE(),GETDATE(),
'');
end catch;

end;

--Execute the procedure will throw error which will put entry in error_handling table
exec testpn;

Check the error handling table output:

Select * from error_handling;