3rd highest salary in sql server

How to Handle Error or Exception in SQL? When you use @@Error and TRY-CATCH?

Today's, I am going to share the code sample for exception handling in SQL Server; The SQL Server exception handling is very similar to the Microsoft C# and C++ etc.

Error Handling Mechanism:-
The two types of error handling in SQL Server that is
1.      @@ERROR
2.      TRY CATCH Block

When we should use @@ERROR?
1.      While executing any stored procedures
2.      In the SQL statements like Select, Insert, Delete and Update etc.
3.      In the Open, Fetch Cursor.

When we should use Try Catch Block?

The Try Catch Block is generally used where want to catch errors for multiple SQL statements.

In the SQL Server, The TRY CATCH are catches all errors and store in the @ERRORS variable and raise the errors using the RAISERROR().

Following functions are used in CATCH block,

ERROR_NUMBER(): Will return error number.
ERROR_SEVERITY():Will return severity level.
ERROR_STATE():Will return state number.
ERROR_LINE():Will return error line number.
ERROR_PROCEDURE():Will return stored procedure name that occurred the error.
ERROR_MESSAGE():Will return full message text of the error.

Syntax:-

BEGIN TRY
 ---YOUR CODE LOGIC
END TRY
BEGIN CATCH
 ---YOUR  ERRORS HANDLER
END CATCH

The example as,

-- ============================================================
-- ABOUT :     TRY CATCH IN SQL SERVER STORED PROCEDURE
-- CREATE DATE: 22/04/2015
-- DESCRIPTION:           RETURNS [LISTEVENT]
-- PARAMETERS:  FROMDATE, TODATE
-- =============================================================
CREATE PROCEDURE  [SPX].[LISTEVENT]
(
 @FROMDATE DATETIME,
 @TODATE DATETIME
)
AS BEGIN
 BEGIN TRY
   SELECT EVENTDESC,
          EVENTNAME,
          STARTDATE,
          ENDDATE
       FROM SPX.SPORTPIXPROMOEVENTS
       WHERE [email protected] AND [email protected]
    END TRY
   
    BEGIN CATCH       
            DECLARE @ERRORS VARCHAR(8000)
            SET @ERRORS= CONVERT(VARCHAR,ERROR_NUMBER()) + '*****' +
            CONVERT(VARCHAR(4000),ERROR_MESSAGE()) + '*****' +
            ISNULL(CONVERT(VARCHAR,ERROR_PROCEDURE()),'GETPROMOTIONLIST') + '*****' +
            CONVERT(VARCHAR,ERROR_LINE()) + '*****' +
            CONVERT(VARCHAR,ERROR_SEVERITY()) + '*****' +
            CONVERT(VARCHAR,ERROR_STATE())

            RAISERROR (@ERRORS, -- MESSAGE TEXT.
                    16, -- SEVERITY.
                    1 -- STATE.
            );
    END CATCH
 END

I hope you are enjoying with this post! Please share with you friends!! Thank you!!!

ANIL SINGH

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Google Amazon and Flipkart Book Store!

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.
ASK Questions
SQL Server NodeJs TypeScript JavaScript Angular SQL Server My Book