sql server - how to write a t-sql master procedure to catch sub procedures raised error

Question

I am new to T-SQL programming. I need to write a main procedures with 5 different sub procedures in it. How could i structure the program so that subprograms will not abort. Instead, they will capture errors and report them back to the main program in the output parameters. Please provide me with pseudo code if you can. Thanks.

Answer 1

something like that BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent.

COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.

ERROR_NUMBER() returns the number of the error.

ERROR_SEVERITY() returns the severity.

ERROR_STATE() returns the error state number.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

-- try block will start
        BEGIN TRY

          BEGIN TRANSACTION TranName
            EXECUTE usp_First;
        -- if sub procedure have some parameter then we can pass 
            EXECUTE usp_Second param1, param2;
            .
            .
            EXECUTE usp_Fifth;
         COMMIT TRANSACTION TranName

        END TRY
        -- if soemthing goes wrong then catch
        BEGIN CATCH 
          IF (@@TRANCOUNT > 0)
           BEGIN
              ROLLBACK TRANSACTION TranName
           END 
           -- get error detail
            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

for more information https://msdn.microsoft.com/en-IN/library/ms175976.aspx