TRY... CATCH IN Sql Server

Overview:

A great option is included in sqlserver 2005 first that was the ability to use TRY .. CATCH block in sql server as C#. It is not easiest thing in early sqlserver. So this options are easy to handles Sql errors.

Explanation

If you are not familiar with the Try...Catch paradigm it is basically two blocks of code with your stored procedures that lets you execute some code, this is the Try section and if there are errors they are handled in the Catch section. 

Let's take a look at an example of how this can be done.  As you can see we are using a basic SELECT statement that is contained within the TRY section, but for some reason if this fails it will run the code in the CATCH section and return the error information.

-- =============================================
-- Author:        <Md Maksudur Rahman>

-- Create date: <20160501>

-- Description:   <Get students>

-- EXEC uspTryCatchTest 'Mohammad'

-- =============================================

CREATE PROCEDURE uspTryCatchTest
   @StudentName VARCHAR(100)

AS

BEGIN TRY
      -- SET NOCOUNT ON added to prevent extra result sets from 
      -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT Std_id, Std_Name 
         From Students  
         WHERE Std_Name Like '%'+@StudentName+'%'

END TRY

BEGIN CATCH

    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;

END CATCH