Page 2 - SQL Server 2000 - Store Procedure - Add new user - check before adding - return value if taken

CREATE Procedure Insertuser
@varUser varchar (50),
@varPass varchar (20)
As

BEGIN TRANSACTION

IF NOT EXISTS (
   SELECT   EMail
   FROM     atblCust
   WHERE   EMail = @varUser
   )
BEGIN
   INSERT INTO atblCust (EMail, Password)
   VALUES (@varUser, @varPass)
END

IF @@ERROR <> 0
BEGIN
--ROLLBACK THE TRANSACTION
ROLLBACK TRANSACTION
--RAISE AN ERROR AND RETURN
RAISERROR ('Could not insert user', 16, 1)
RETURN
END
COMMIT TRANSACTION
GO

CREATE Procedure Insertuser
@FName varchar (50), 
@LName varchar (50), 
@PassWord  varchar (20),
@Email varchar (50),
@ShipName varchar (100),
As


DECLARE @err1 INT
DECLARE @err2 INT 

BEGIN TRANSACTION
        BEGIN TRANSACTION
		IF NOT EXISTS (
  		 SELECT   EMail
   		FROM     Customer
  		 WHERE   EMail = @Email)

		INSERT INTO atblCustomer (FName, LName, Email, Password)
		VALUES (@FName, @LName, @Email, @Password)
		SET @err1 = @@ERROR 
		IF @err1 <> 0
            			BEGIN
               			 IF @err1 = 547
               		 		PRINT 'A constraint error has occurred.'
               		 	ELSE
                    				PRINT 'An unspecified error has occurred.'
                	ROLLBACK TRANSACTION
                RETURN @err1
            END
        ELSE
            COMMIT TRANSACTION

	BEGIN TRANSACTION
 		 INSERT INTO atblShipAddress (ShipName)
   		 VALUES (@ShipName)
             		 SET @err2 = @@ERROR 
			IF @err2 <> 0
            			BEGIN
               			 IF @err2 = 547
                   		 		PRINT 'A constraint error has occurred.'
                			ELSE
                    				PRINT 'An unspecified error has occurred.'
                ROLLBACK TRANSACTION
                RETURN @err2
            END
        ELSE
            BEGIN
                COMMIT TRANSACTION 
RETURN 0
            END
IF (@err1 <> 0) OR (@err2 <> 0)
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION
GO

DECLARE @intErrorCode INT

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Original - SQL Code CREATE Procedure Insertuser @FName varchar (50), @LName varchar (50), @PassWord varchar (20), @Email varchar (50), @ShipName varchar (100), As DECLARE @err1 INT -- If the email is already then return some unique -- error that we can check or. IF EXISTS ( SELECT EMail FROM Customer WHERE EMail = @Email) BEGIN RETURN -10101 END BEGIN TRANSACTION -- Email Appears to be unique so -- Insert the users information INSERT INTO atblCustomer (FName, LName, Email, Password) VALUES (@FName, @LName, @Email, @Password) SET @err1 = @@ERROR -- If the insert fails... IF @err1 <> 0 BEGIN -- ..specfiicaly check if the violated uniqueness IF @err1 = 547 PRINT 'A constraint error has occurred.' ELSE PRINT 'An unspecified error has occurred.' -- Rollback any changes we may have made ROLLBACK TRANSACTION -- Return a non-zero to show error RETURN @err1 END -- The insert of users info when well so -- insert the shipping address INSERT INTO atblShipAddress (ShipName) VALUES (@ShipName) SET @err1 = @@ERROR -- If the insert fails/// IF @err1 <> 0 BEGIN -- specifically check for violated uniqueness IF @err2 = 547 PRINT 'A constraint error has occurred.' ELSE PRINT 'An unspecified error has occurred.' -- Rollback the entire transaction (i.e. both inserts) ROLLBACK TRANSACTION -- Return on-zero to show error RETURN @err1 END -- Both inserts when OK, so commit the changes COMMIT TRANSACTION -- Show we completed OK RETURN 0

CREATE PROCEDURE Insertuser
@FName varchar (50), 
@LName varchar (50), 
@PassWord  varchar (20),
@Email varchar (50),
@ShipName varchar (100),
AS
 
DECLARE @err1 INT
 
    -- If the email is already then return some unique
    -- error that we can check or.
    IF EXISTS (
        SELECT   EMail
       FROM   Customer
       WHERE   EMail = @Email)
    BEGIN
      RETURN -10101
    END
    
    BEGIN TRANSACTION
        -- Email Appears to be unique so 
        -- Insert the users information
        INSERT INTO atblCustomer (FName, LName, Email, Password)
             VALUES (@FName, @LName, @Email, @Password)
        SET @err1 = @@ERROR 
        
        -- If the insert fails...
        IF @err1 <> 0
        BEGIN
            -- ..specfiicaly check if the violated uniqueness
            IF @err1 = 547
             PRINT 'A constraint error has occurred.'
            ELSE
             PRINT 'An unspecified error has occurred.'