Stored Procedure Error Handling

OK, i'm trying to do some error checking on stored procedures and am
following the advise in Erland Sommarskog's 'Implementing Error
Handling with Stored Procedures' document.

Can anybody help with my stored procedures and why it keeps erroring at
the '-- Create new Address Detail stage'? The errorCode value that is
being return in my web app is 0, so i'm not even sure why it's even
raising the error!!

Rather than executing the INSERT INTO AddressDetail in my
CreateSupplier procedure and checking for errors, i'd like to be able
execute a CreateAddressDetail SP, so that i can reuse it throughout my
web app.

New suppliers must have a contact address associated with it, so if
there's an error creating the suppliers address, i need my
CreateSupplier stored procedure to ROLLBACK and not create the new
supplier. That's why i'm not doing two separate calls to the procedures
from my app code.

Any suggestions are most appreciated.

Many thanks

Dan Williams.



CREATE PROCEDURE CreateSupplier
@supplierName varchar(50),
@userId bigint,
@address varchar(50),
@town varchar(50),
@county varchar(50),
@postCode varchar(15),
@contactName varchar(50)
AS
BEGIN

DECLARE @newSupplierId as bigint
DECLARE @newAddressDetailId as bigint
DECLARE @errorCode as bigint

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO Supplier
(supplierName, accOpenedBy, accOpenedDate)
VALUES (@supplierName, @userId, getDate())

SET @newSupplierId = SCOPE_IDENTITY()

-- Check for an error creating new supplier
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0) BEGIN ROLLBACK TRAN RAISERROR ('Error creating
supplier',16,1) RETURN @errorCode END

-- Create new Address Detail
EXEC @errorCode = CreateAddressDetail @address, @town, @county,
@postCode, @contactName, @newAddressDetailId OUTPUT

SELECT @errorCode = coalesce(nullif(@errorCode, 0), @@error)

if @errorCode <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error creating
address. ErrorCode = %d',16, @errorCode) RETURN @errorCode END

COMMIT TRAN
SET NOCOUNT OFF
RETURN @newSupplierId

END
GO



CREATE PROCEDURE CreateAddressDetail
@address varchar(50),
@town varchar(50),
@county varchar(50),
@postCode varchar(15),
@contactName varchar(50),
@newAddressDetailId bigint OUTPUT

AS
BEGIN

-- Create new AddressDetail

DECLARE @errorCode as bigint

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO AddressDetail
(address, town, county, postCode, contactName)
VALUES (@address, @town, @county, @postCode, @contactName)

SET @newAddressDetailId = SCOPE_IDENTITY()

-- Check for an error creating new address
SELECT @errorCode = @@ERROR
IF (@errorCode <> 0)
BEGIN
RAISERROR ('Error creating new address detail',16,1)
ROLLBACK TRAN
END
ELSE
COMMIT TRAN
SET NOCOUNT OFF
RETURN @newAddressDetailId
END
GO