Posted by laurenq uantrell on 11/06/53 11:27

I'm wondering if I'm doing this right. Wondering about the transactions
and error handling. (Do I even need to put BEGIN TRANSACTION AND
COMMIT TRANSACTION in there?)

I think that this sproc is causing occasional blocking:

Alter Procedure sprocINSERTSTUFF
@Col1Data int = Null,
@Col2Data nvarchar(255) = Null,
@Col3Data ntext = Null,
@UniqueID int OUTPUT

AS

set nocount on
set xact_abort on

DECLARE @err int
DECLARE @ServerDate DateTime
SELECT @ServerDate = GETUTCDATE()

BEGIN TRANSACTION


INSERT INTO
tblStuff (Col1, Col2, Col3, DateCreated, etc.)
VALUES
(@Col1Data, @Col2Data, @Col3Data, @ServerDate, etc.)

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@err END

SELECT @UniqueID = SCOPE_IDENTITY()

COMMIT TRANSACTION

BEGIN TRANSACTION

INSERT INTO
tblMoreStuff (UniqueID, DateCreated, Col1, Col2, Col3)
Values
(@UniqueID, @ServerDate, @Col1Data, @Col2Data, 'Text Label: ' +
isnull(Cast(@Col3Data AS nvarchar(4000)),'<none>')

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@err END

COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err

[Back to original message]