CREATE PROCEDURE insertCustomer
 @pin varchar(10),
 @firstname varchar(30),
 @lastname varchar(30),
 @ttsSex char(1),
 @phoneNpa char(3),
 @phoneNumber char(7),
 @phoneExt varchar(5),
 @customerId int OUTPUT
AS
SET NOCOUNT ON

DECLARE @trackingOrder int
DECLARE @remindersSortOrder varchar(50) 
DECLARE @phoneOptIn char(1)
DECLARE @emailOptIn char(1)
DECLARE @memberSince datetime 

DECLARE @err_cd int 

SET @customerId = 0
SET @trackingOrder = 1
SET @remindersSortOrder = '' 
SET @phoneOptIn = 'N'
SET @emailOptIn = 'N'
SET @memberSince = getdate()

BEGIN TRAN

-- insert new customer
INSERT INTO customer VALUES(@pin, @firstname, @lastname, @memberSince)
SET @err_cd=@@ERROR

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

-- get customer_id just created
SET @customerId=@@IDENTITY

-- insert new customer account
INSERT INTO customer_account VALUES(@customerId, @remindersSortOrder, @ttsSex, @phoneOptIn, @emailOptIn)
SET @err_cd=@@ERROR

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

-- insert new customer phone
INSERT INTO customer_phone VALUES(@customerId, @phoneNpa, @phoneNumber, @phoneExt, @trackingOrder)
SET @err_cd=@@ERROR

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

COMMIT TRAN
GO