Tsql operation

Question

To transfer the stored procedure to the transaction, I add the following:

CREATE PROCEDURE [dbo].[P_ORD_InsertTextField]
    //PARAMS
AS
BEGIN
    BEGIN TRY
    BEGIN TRANSACTION

    //STP BODY

    COMMIT
    END TRY
    BEGIN CATCH
      IF @@TRANCOUNT > 0
         ROLLBACK

      DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
      SELECT @ErrMsg = ERROR_MESSAGE(),
             @ErrSeverity = ERROR_SEVERITY()

      RAISERROR(@ErrMsg, @ErrSeverity, 1)
    END CATCH
END
GO

Is there a shorter way that does the same? it is a huge block of code for "just" transaction processing.

Answer 1

No, that is pretty much it.

You can hide the @ErrMsg processing behind the stored proc or UDF, and you do not need the @ErrSeverity processing. This is usually 16, which is a "user error"

See also my answer: Nested stored procedures containing the TRY CATCH ROLLBACK pattern?