Update 2 tables in a transaction

Yuiko was writing a query the other day to update 2 tables at the same time.

ALTER PROCEDURE [dbo].[usp_PracticeBillingAddress_Update]

(

@PracticeID INT

, @AddressID INT

, @AttentionOf VARCHAR(50)

, @AddressLine1 VARCHAR(50)

, @AddressLine2 VARCHAR(50)

, @City VARCHAR(50)

, @State CHAR(2)

, @ZipCode CHAR(5)

, @ZipCodePlus4 CHAR(4)

, @ModifiedUserID INT

)

AS

BEGIN

DECLARE @TransactionCountOnEntry INT -- Transaction Count before the transaction begins

, @Err INT -- holds the @@Error code returned by SQL Server

SELECT @Err = @@ERROR

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF @Err = 0

BEGIN

SELECT @TransactionCountOnEntry = @@TRANCOUNT

BEGIN TRANSACTION

END

IF @Err = 0

BEGIN

UPDATE dbo.Address

SET

AddressLine1 = @AddressLine1

, AddressLine2 = @AddressLine2

, City = @City

, State = @State

, ZipCode = @ZipCode

, ZipCodePlus4 = @ZipCodePlus4

, ModifiedUserID = @ModifiedUserID

, ModifiedDate = GETDATE()

WHERE

AddressID = @AddressID

SET @Err = @@ERROR

END

IF @Err = 0

BEGIN

UPDATE dbo.PracticeBillingAddress

SET

AttentionOf = @AttentionOf

, ModifiedUserID = @ModifiedUserID

, ModifiedDate = GETDATE()

WHERE

PracticeID = @PracticeID

AND AddressID = @AddressID

SET @Err = @@ERROR

END

Exec usp_PracticeBillingAddress_UpdateCentralDecentral @practiceID, @ModifiedUserID

IF @@TranCount > @TransactionCountOnEntry

BEGIN

IF @Err = 0

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

-- Add any database logging here

END

RETURN @Err

END