We often try to find out the stored procedure name, the line number in stored procedure which caused the error, the error message to identify the reason of error, the severity of the error etc. This all information can be achieved.

Step 1: Create a table Test with column name and age.


CREATE Table Test(name VARCHAR(10), age INT)

Step 2: Write a stored procedure TestError. We will try to insert 'PQR' in age column which is int to generate error.

CREATE PROC TESTError
AS
BEGIN
      BEGIN TRY
            INSERT INTO Test(name,age) VALUES('ABC','PQR')
      END TRY
      BEGIN CATCH
            SELECT ERROR_LINE()
            SELECT ERROR_MESSAGE()
            SELECT ERROR_NUMBER()
            SELECT ERROR_PROCEDURE()
            SELECT ERROR_SEVERITY()
            SELECT ERROR_STATE()
      END CATCH
END

Step 3:  Now execute the SP, EXEC TESTError

I found this very useful to identy the root cause of issue in stored procedure.