Try and Catch block in sql

Here, one of important things is "Why should we use this try and catch ?"
Because sql server already provided Error handler. If we write a wrong code or syntax in sql server window..then we will get some error indignation immediately.

In various transnational scope this "Try catch" is more important. Because through this we can write our custom action in sql.

For example, while booking train ticket you filled up entire details in reservation form then save the details and while go for payment option it is getting failure. Now is the ticket book for you?

BEGIN TRY
     
       1. Passengers details filled up
       2. Complete internet payment
       3. Book() commit

END TRY

BEGIN CATCH  
     
        Book() role back

END CATCH

BEGIN TRY

       SELECT 1/0;   

   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

Output:

ErrorNumber     ErrorSeverity    ErrorState    ErrorProcedure       ErrorLine     ErrorMessage

8134                         16                         1                     NULL                 2           Divide by zero

Here One more real time example given below:

For SQL server User creation in SSMS:

  begin try

   if not exists (select 1 from master.sys.syslogins where name='Sql_user_Pritam')

   --login not existing

       

             execute('create login Sql_user_Pritam with password=''##########'', check_policy=on')

  --commit

    commit transaction

  end try

 

begin catch

    --rollback on error

    rollback transaction

    declare @ErrorMessage varchar(max)

    set @ErrorMessage=error_message()

    raiserror(@ErrorMessage, 16, 1)

  end catch