The syntax for Try..Catch is given below:-

BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
     [ { sql_statement | statement_block } ]
END CATCH
[ ; ]
For example, suppose we have a table employeedetails whose structure is given below:

CREATE table employeedetails (id int identity(1,1),empsignum nvarchar(20), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

Now, for the demo purpose, we can insert or update data in this table with the help of a stored procedure whose script is given below:-

CREATE PROCEDURE Demo_exceptionhandling
(
@empsignum nvarchar(20),
@Fname nvarchar(100),
@Ename nvarchar(100)

)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
   IF EXISTS (select 1 from employeedetails where empsignum=@empsignum)
     BEGIN
       Update employeedetails set empFname=@Fname ,empEname=@Ename  where empsignum=@empsignum
     END
   ELSE
      BEGIN
       Insert into employeedetails ( empsignum, empFname  ,  empEname  , empdate )
       Values (@empsignum, @Fname, @Ename, getdate())
      END
   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
   SET NOCOUNT OFF
 END
In the above stored procedure if any error occurs within the try block, it will automatically move to CATCH block where we can handle the error messages with the defined Error Functions in CATCH block. If we want we can save the values return by these error function into a table for future references.These Error Functions are given below:-

ERROR_LINE() - Return error line number of SQL query which cause to raise error.

ERROR_NUMBER() - Return error number which is unique and assigned to it.

ERROR_SEVERITY() - Return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() - Return state number of error message which cause to raise error.

ERROR_PROCEDURE() - Return name of the procedure where an error occurred.

ERROR_MESSAGE() - Return the complete text of the error message which cause to raise error.

We can also use TRY... CATCH block with transactions where we can write the ROLLBACK command within the CATCH block so that all the uncommitted transactions become rollback in case of any error.

For example, in the below SQL script, we have implemented the transaction in the stored procedure which we have mentioned above.

CREATE PROCEDURE Demo_exceptionhandling
(
@empsignum nvarchar(20),
@Fname nvarchar(100),
@Ename nvarchar(100)

)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRANSACTION

BEGIN TRY
   IF EXISTS (select 1 from employeedetails where empsignum=@empsignum)
     BEGIN
       Update employeedetails set empFname=@Fname ,empEname=@Ename  where empsignum=@empsignum
     END
   ELSE
      BEGIN
       Insert into employeedetails ( empsignum, empFname  ,  empEname  , empdate )
       Values (@empsignum, @Fname, @Ename, getdate())
      END
 
  END TRY
  BEGIN CATCH

  IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION

        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
    ,GETDATE()
    
   END CATCH

   IF @@TRANCOUNT > 0
    BEGIN
     commit transaction
    END
   SET NOCOUNT OFF
 END
TRY…CATCH do not handle the following conditions:

1) Warnings or informational messages that have a severity of 10 or lower.
2) Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session.
3) Attentions, such as client-interrupt requests or broken client connections.
4) When the session is ended by a system administrator by using the KILL statement.
5) Compile errors, such as syntax errors, that prevent a batch from running.
6) Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.