alter PROCEDURE updateAccount
 @customerId int,
 @pin varchar(10),
 @firstname varchar(30),
 @lastname varchar(30),
 @ttsSex char(1),
 @phoneNpa char(3),
 @phoneNumber char(7),
 @phoneExt varchar(5),
 @phoneOptIn char(1),
 @emailOptIn char(1)
AS
SET NOCOUNT ON

DECLARE @trackingOrder int
DECLARE @remindersSortOrder varchar(50) 
DECLARE @memberSince datetime 

DECLARE @err_cd int 

SET @trackingOrder = 1
SET @remindersSortOrder = '' 
SET @memberSince = getdate()

BEGIN TRAN

-- update customer data
UPDATE customer 
SET pword=@pin, firstName=@firstname, lastName=@lastname
WHERE customerId = @customerId

SET @err_cd=@@ERROR

IF @err_cd <> 0 
BEGIN
   ROLLBACK TRAN
   RETURN(@err_cd)
END

-- update customer account
UPDATE customer_account 
SET ttsSex = @ttsSex, phoneOptIn = @phoneOptIn, emailOptIn = @emailOptIn
WHERE customerId = @customerId

SET @err_cd=@@ERROR

IF @err_cd <> 0 
BEGIN
   ROLLBACK TRAN
   RETURN(@err_cd)
END

-- update customer phone
UPDATE customer_phone 
SET phoneNpa = @phoneNpa, phoneNumber=@phoneNumber, phoneExt = @phoneExt
WHERE customerId = @customerId and trackingOrder = @trackingOrder

SET @err_cd=@@ERROR

IF @err_cd <> 0 
BEGIN
   ROLLBACK TRAN
   RETURN(@err_cd)
END

COMMIT TRAN

GO