Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Try...Catch

Try...Catch:

Error handling for T-SQL similar to C#, Java, C++. Group of SQL statement enclosed

Inside try block, if error occurs in try block control is passed to another Sql. statement

Enclosed in catch block. 

Syntax:

BEGIN TRY
     { T-Sql_ statement | statement_block }
END TRY
BEGIN CATCH
     [ { T-Sql _statement | statement_block } ]
END CATCH
[ ; ]

 

Arguments:

1.       Any T-SQL statement

2.       Any T-SQL statement enclosed in begin..end block.

Points to remember:

1. GOTO statement cannot be used to enter try…Catch, GOTO can be used to

Jump label inside the same TRY or CATCH block or to leave a TRY or CATCH block.

2. TRY...CATCH construct cannot be used in user defined function.

3.  TRY...CATCH construct catches all execution errors that have a severity greater than 10 that do not close the database connection.

4. TRY...CATCH construct can be nested ,either try block or catch block contain nested try catch construct.

5.  TRY...CATCH  block allows multiple blocks and if else also but avoid below:

---Avoid this with GO
 BEGIN TRY
 
    PRINT 'statement 1'
 
    GO -- Cannot have a GO to end batch inside of a TRY / CATCH
 
    PRINT 'statement 2'
 
END TRY
BEGIN CATCH
 
    PRINT 'Catch Block'
 
END CATCH
 
---Avoid this with Begin…End:
   IF (1 = 1)
    BEGIN
     BEGIN TRY
 
     PRINT 'test'
 
    END -- Cannot END a block that began prior to the TRY / CATCH
 
    END TRY
      BEGIN CATCH
 
      PRINT 'Catch block'
 
      END CATCH
 
---Avoid this with IF..Else
 
    IF (1 = 1)
    BEGIN TRY
 
        PRINT 'statement 1'
 
    ELSE -- Cannot do ELSE for an IF that started prior to the TRY / CATCH
        PRINT 'statement 2'
 
    END TRY
    BEGIN CATCH
 
        PRINT 'Catch Block'
 
    END
CATCH

  6. TRY...CATCH block don catch error or handle error for following condition

       a. Warning message, information message below 10 severities (reason database connection closed).
       b. When session ended by Kill statement.
       c. Client interrupt requests or broken client connections.
      d. Errors with severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If errors with severity of 20 or higher and the database connection is not disrupted  then TRY...CATCH block handles errors. 

Throw Statement:

Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server (SQL Server 2012+)

 more info...http://www.codechef4u.com/post/2015/04/13/throw-t-sql.aspx

RAISERROR:

Used to return error message back to application,system warning or error message generated by Sql server database engine.

 more info...http://www.codechef4u.com/post/2015/04/06/raiserror.aspx

Try-Catch Example with RAISERROR:

-- =============================================
-- Author:            Nagnath Kendre
-- Create date: <Create Date,,>
-- Description:     Insert's Emplyee Detail, exception handlled with try catch block and RAISERROR
-- =============================================
CREATE PROCEDURE InsertEmplyeeDetails_SP
          -- Add the parameters for the stored procedure here
          @EmployeeName Nvarchar(250),
          @Country Nvarchar(50),
          @PrimaryLanguage Nvarchar(150),
          @CompanyPlant Nvarchar(150),
          @ManagerId int
AS
BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
 SET NOCOUNT ON;
 BEGIN TRY
 BEGIN TRANSACTION
  INSERT INTO Employee
([EmployeeName],[Country],[PrimaryLanguage],[CompanyPlant],[ManagerId],[CreatedDate])
 VALUES (@EmployeeName,@Country,@PrimaryLanguage,@CompanyPlant,@ManagerId,GETDATE())
 
 COMMIT TRANSACTION;
 END TRY
 
BEGIN CATCH
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
 
    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
 
    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
 
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
 
END CATCH
 
END
GO

Result:

EXEC      [dbo].[InsertEmplyeeDetails_SP]
                                @EmployeeName = N'Sanskruti',
                                @Country = N'INDIA',
                                @PrimaryLanguage = N'Marathi',
                                @CompanyPlant = N'Pune',
                                @ManagerId = abd 


Invalid entry,please enter valid data.

Loading