tsql transaction

Question

In order to wrap stored procedure in a 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 any shorter way that does the same? this is a huge code block for "just" handle a transaction..

score 4 · Accepted Answer · edited May 23 '17 at 12:16

4

No, this is pretty much it.

You can hide the @ErrMsg processing behind a stored proc or UDF, and you don't need @ErrSeverity processing. It is normally 16 which is "user defined error"

See my answer here too please: Nested stored procedures containing TRY CATCH ROLLBACK pattern?

edited May 23 '17 at 12:16

Community

1
1

answered Mar 13 '11 at 12:50

gbn

422,506
82
585
676

@gbn: What is the reason for NO COUNT in your answer? – Naor Mar 13 '11 at 12:59
1

@Naor: The "xx rows affected" returns an extra recordset to the client. It also breaks some ORMs and some clients. See my question here please http://stackoverflow.com/q/1483732/27535 – gbn Mar 13 '11 at 13:01
@gbn: So you put SET NOCOUNT in every stored procedure you have? – Naor Mar 13 '11 at 13:18
@Naor: yes, and SET XACT_ABORT ON too – gbn Mar 13 '11 at 13:19
@gbn: I mean, you put SET NOCOUNT in every stored procedure even if it doesn't contains transaction? – Naor Mar 13 '11 at 14:43
@Naor: yes, and SET XACT_ABORT ON too. A transaction has nothing to so with SET NOCOUNT ON – gbn Mar 13 '11 at 14:44

tsql transaction

1 Answers1