SQL Server Exception Handling by TRY-CATCH

SQL Server Exception Handling by TRY…CATCH


The SQL is the set of instructions that are used to interact with a database. SQL (Structured Query Language) is the only language that most databases actually clearly understand. Whenever we interact with a database, the software translates your commands (whether they used GUI comment or queries) into SQL statement that the database knows how to work. Now we try to consider maximum here. I think you can get useful and important information here.

Exception Handling by TRY…CATCH:

SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks.

SQL Server Exception Handling Syntax:

BEGIN TRY
--T-SQL statements
--or T-SQL statement blocks
END TRY
BEGIN CATCH
--T-SQL statements
--or T-SQL statement blocks
END CATCH

SQL Server Exception Handling Example :

IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
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
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;

END CATCH;

Other Related Post:

Comments