Use Exception handling in SQL Server

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;

1 thought on “Use Exception handling in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.