Mike,

Thanks a lot for your help. This works (my concern, though about CredID - it's int, does it do implicit cast operation)?
I removed some comments...

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'USP_Insert_ShoppingCartInfo' 
	   AND 	  type = 'P')
    DROP PROCEDURE USP_Insert_ShoppingCartInfo
GO

CREATE PROCEDURE USP_Insert_ShoppingCartInfo(
	@tcUserID  varchar(20),
        @tnNumOfRecords int,
        @tcDescription 	text,
        @tcCreditCategory char(1),
	@tcSelectionCriteria text,
        @tcListOfCodes varchar(500),
        @tnCartID int OUTPUT)
AS    

SET NOCOUNT ON -- Do not want to show 1 row affected message
-- First do basic validation

 IF @tcUserID IS NULL
      BEGIN
-- Mike Levy suggests
        RAISERROR ('ERROR: The UserID value must contain a value.',16,1)
        RETURN 1
      END

 -- Check all 3 tables existance
   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

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

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

-- Now do the actual updates/inserts and check for possible errors code

    BEGIN TRANSACTION
/*
   If exists, grab CartID and update LastModified field
   otherwise insert a record and grab generated CartID
*/

-- Code from Mike Levy 
DECLARE @Err int, @CartContentID int

-- PK in the Cart table
SET @tnCartID = 0 
-- PK in the CartContent table
SET @CartContentID = 0 
-- Error code
SET @Err = 0 

IF EXISTS (SELECT CartID FROM cart WHERE UserID = @tcUserID)
   BEGIN
   	UPDATE cart
   	SET @tnCartID = CartID, 
   	LastModTs = CURRENT_TIMESTAMP, 
	ModiType = 'U'   
   	WHERE UserID = @tcUserID
        SELECT @Err = @@ERROR

        IF @Err !=0 -- Error occurred 
            BEGIN
              RAISERROR ('Can not update Cart table for the User %s' ,16,1, @tcUserID) 
             GOTO _Cleanup 
           END
   END
ELSE 
-- New User
   BEGIN
	 INSERT INTO cart (UserID, ModiType) values (@tcUserID,'I')
         SELECT @Err = @@ERROR, @tnCartID = SCOPE_IDENTITY()
                
        IF @Err !=0 -- Error occurred 
            BEGIN
              RAISERROR ('Can not Create Cart for the User %s' ,16,1, @tcUserID) 
             GOTO _Cleanup 
           END
    END

-- Now continue with CartContent table

   INSERT INTO CartContent 
            (CartID, NumOfRecords, 
            CreditCategory, DescriptionText, SelectionCriteria)
            values (@tnCartID, @tnNumOfRecords, @tcCreditCategory, 
                    @tcDescription, @tcSelectionCriteria)
     SELECT @Err = @@ERROR, @CartContentID = SCOPE_IDENTITY()

     IF @Err !=0 -- Error occurred 
         BEGIN
              RAISERROR ('Can not add CartContent for the User %s'  ,16,1, @tcUserID) 
              GOTO _Cleanup 
         END

-- Parse list of codes and insert into CartDetail table
INSERT INTO CartDetail (CartContentID, CreditID) 
       SELECT @CartContentID , value 
             FROM fn_Split(@tcListOfCodes,',')      
     SELECT @Err = @@ERROR

     IF @Err !=0 -- Error occurred 
         BEGIN
              RAISERROR ('Can not add list of IDs into CartDetail for the User %s'  ,16,1, @tcUserID) 
              GOTO _Cleanup 
         END
_Cleanup:
IF @err != 0
  BEGIN
    ROLLBACK TRANSACTION
    RETURN @Err -- Return actual Error code - could be helpful
  END
ELSE
 BEGIN
   COMMIT TRANSACTION
   RETURN 0
END
GO
-- Now test procedure
declare @tnCartID int, @RetStat int 

EXECUTE @RetStat = USP_Insert_ShoppingCartInfo 
	'241111111111'  ,
        5 ,
        'Some descr' 	,
        'L' ,
	'some  crit' ,
        '1,2,3' ,
        @tnCartID OUTPUT


print 'Cart ID =' + RTRIM(CONVERT(varchar(30), @tnCartID)) + ' Error code = ' + RTRIM(CONVERT(varchar(30), @RetStat))
GO