Ok, here is the new version. Is it better? I also not sure, about 3rd piece for inserting multiple codes (passed as comma-delimited list) into CartDetail table. For now I concentrated on the first part (Cart and CartContent)

CREATE PROCEDURE USP_Insert_ShoppingCartInfo(
	@tcUserID  char(20),
        @tnNumOfRecords int,
        @tcDescription 	text,
        @tcCreditCategory char(1),
	@tcSelectionCriteria text,
        @tcListOfCodes varchar(500),
        @tnCardID int OUTPUT)
AS 
        
--	<@param2, sysname, @p2> <datatype_for_param2, , int>  OUTPUT
-- SELECT @p2 = @p2 + @p1

/*
CREATED BY:    Nadya Nosonovsky
CREATED ON:    28/2/2003
PURPOSE:    This routine inserts records into Cart, CartContent and CartDetail tables
            It passes UserID, Description, SelectionCriteria, NumberOfRecords, 
            and list of CredID separated by comma to insert into CartDetail table
*/
-- First do basic validation
BEGIN
    IF @tcUserID IS NULL
      BEGIN
-- Mike Levy suggests
        RAISERROR ('ERROR: The UserID value must contain a value.',16,1)
        RETURN 1
      END

    IF NOT EXISTS 
         (SELECT * FROM INFORMATION_SCHEMA.TABLES 
          WHERE TABLE_NAME = 'Cart')
        BEGIN  
          RAISERROR ('ERROR: The Cart table does not exist.',16,1)
          RETURN 2 
        END

    BEGIN TRANSACTION
/*
   If exists, grab CartID and update LastModified field
   otherwise insert a record and grab generated CartID
*/
/* The code bellow belongs to Bob Archer
   See Title: Re: SP with multiple tables and databases 
              (insert & delete) Thread #758108 Message #758212 
   According to Mike Levy another variant (see bellow)
   will be much better on your locks and concurrency

   UPDATE cart
   SET LastModTs = CURRENT_TIMESTAMP,
   @CartID = CartID
   FROM cart
   WHERE UserID = @tcUserID

   IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            PRINT 'ERROR: An error occurred while attempting to update the Cart Table.'
            RETURN 3
        END

    IF @@RowCount = 0 --Record doesn't exist
        BEGIN
	   INSERT INTO cart (UserID) values (@tcUserID)

	   SELECT @CartID = SCOPE_IDENTITY()
        END
*/

-- Code from Mike Levy 
DECLARE @CartID  int, @InsErr int, @UpdErr int, @RowCountVar int

SET @CartID = 0
SET @InsErr = 0
SET @UpdErr = 0
SET @RowCountVar = 0

IF EXISTS (SELECT CartID FROM cart WHERE UserID = @tcUserID)
   BEGIN
   	UPDATE cart
   	SET @CartID = CartID, 
   	LastModTs = CURRENT_TIMESTAMP   
   	WHERE UserID = @tcUserID
        SELECT @UpdErr = @@ERROR, @RowCountVar = @@ROWCOUNT
   END
ELSE 
-- New User
    BEGIN
	 INSERT INTO cart (UserID) values (@tcUserID)
         SELECT @InsErr = @@ERROR, @RowCountVar = @@ROWCOUNT, 
	        @CartID = SCOPE_IDENTITY()
     END
/* Now we need to check, if the above statements didn't produce an error
   If they do, return prematuraly
*/     
IF @UpdErr <> 0 OR @InsErr <> 0 OR @RowCountVar = 0 
  BEGIN
   -- An error occurred. Indicate which operation(s) failed
   -- and roll back the transaction.
   IF @UpdErr <> 0 
      
      RAISERROR ('An error occurred during execution of the Update statement for Cart table.',16,1) 

   IF @InsErr <> 0
      BEGIN
         RAISERROR ('An error occurred during execution of the INSERT statement for Cart table.',16,1) 
         SET @UpdErr = @InsErr
       END
   ROLLBACK TRAN
   RETURN @UpdErr -- Return an actual error code
END
-- Now continue with CartContent table
   INSERT INTO CartContent 
            (CartID, NumOfRecords, 
            CreditCategory, DescriptionText, SelectionCriteria)
            values (@CartID, @tnNumOfRecords, @tcCreditCategory, 
                    @tcDescription, @tcSelectionCriteria)
     SELECT @InsErr = @@ERROR, @RowCountVar = @@ROWCOUNT    

     IF @InsErr <> 0
        BEGIN
            ROLLBACK TRAN
            PRINT 'ERROR: An error occurred while attempting to insert record to the CartContent Table.'
            RETURN @InsErr
        END
          
     COMMIT TRANSACTION
     RETURN 0
END