T-SQL Stored Procedure Template from MSDN (with comments)

msdn_sproc_template_with_comments.sql

--Procedure: schema_name.stored_procedure_name --<<EDIT stored_procedure_name
CREATE PROCEDURE schema_name.stored_procedure_name --<<EDIT stored_procedure_name
    /**
     * @ParameterOne    DATATYPE        --<<EDIT parameters
     * ,@ParameterTwo   DATATYPE
     * ,...
     **/
AS
    --Configure pertinent session options
    SET XACT_ABORT OFF;
    SET NOCOUNT ON;
    /**
     * Detect whether the procedure was called
     * from an active transaction and save
     * that for later use.
     * In the procedure, @TranCounter = 0
     * means there was no active transaction
     * and the procedure started one.
     * @TranCounter > 0 means an active
     * transaction was started before the
     * procedure was called.
     **/
    DECLARE @TranCounter INTEGER = @@TRANCOUNT;
    IF @TranCounter > 0
        /**
         * Procedure called when there is
         * an active transaction.
         * Create a savepoint to be able
         * to roll back only the work done
         * in the procedure if there is an
         * error.
         **/
        SAVE TRANSACTION stored_procedure_name_savepoint;
    ELSE
        /**
         * Procedure must start its own
         * transaction.
         **/
        BEGIN TRANSACTION;
    /* Modify database. */
    BEGIN TRY

        /********************************************************/
        /*******************ADD CODE HERE************************/
        /********************************************************/

        /**
         * Get here if no errors; must commit
         * any transaction started in the
         * procedure, but not commit a transaction
         * started before the transaction was called.
         **/
        IF @TranCounter = 0
            /**
             * @TranCounter = 0 means no transaction was
             * started before the procedure was called.
             * The procedure must commit the transaction
             * it started.
             **/
            COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        /**
         * An error occurred; must determine
         * which type of rollback will roll
         * back only the work done in the
         * procedure.
         **/
        IF @TranCounter = 0
            /**
             * Transaction started in procedure.
             * Roll back complete transaction.
             **/
            ROLLBACK TRANSACTION;
        ELSE
            /**
             * Transaction started before procedure
             * called, do not roll back modifications
             * made before the procedure was called.
             **/
            IF XACT_STATE() <> -1
                /**
                 * If the transaction is still valid, just
                 * roll back to the savepoint set at the
                 * start of the stored procedure.
                 **/
                ROLLBACK TRANSACTION excptn_reason_upsrt_savepoint;
                /**
                 * If the transaction is uncommitable, a
                 * rollback to the savepoint is not allowed
                 * because the savepoint rollback writes to
                 * the log. Just return to the caller, which
                 * should roll back the outer transaction.
                 **/

        /**
         * After the appropriate rollback, echo error
         * information to the caller.
         **/
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        RAISERROR (
            @ErrorMessage       -- Message text.
            ,@ErrorSeverity     -- Severity.
            ,@ErrorState        -- State.
        );
    END CATCH;