Why

UPDATE cart
>SET LastModified = CURRENT_TIMESTAMP
>,@CartID = cartid
>FROM cart
>WHERE userid = @tnUserID
>
>IF @@RowCount = 0
>BEGIN
> INSERT INTO cart blah blah blah
> @cartid = @@SCOPE_IDENTITY
>END

you'd be better off with

IF EXISTS (SELECT * FROM cart WHERE cartid = @cartID)
>UPDATE cart
>SET LastModified = CURRENT_TIMESTAMP
>,@CartID = cartid
>FROM cart
>WHERE userid = @tnUserID
ELSE
>INSERT INTO cart blah blah blah
>@cartid = @@SCOPE_IDENTITY

It'll be much better on your locks and concurrency

----------------------------------------------
Why your suggestion is better? If UserID exists, we would need to modify, so we don't have to search?

Also, the definition changed. I now don't need to use the second database. I just want to do 3 inserts into the three tables, passing info for the last table as comma-delimited list.

Here is how I'm starting, trying to adopt Bob's idea. Since it's my first SP in SQL Server, please, bear with me:

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

Incorrect syntax near UserID

INSERT INTO cart UserID values @tcUserID
@CartID = @@SCOPE_IDENTITY

Incorrect syntax near CartID. What's wrong here?

Also am I getting right, that you don't have to close IF statements, e.g. is this valid:

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
	   @CartID = @@SCOPE_IDENTITY
        END

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

     INSERT INTO CartContent 
            CartID, NumOfRecords, CreditCategory, DescriptionText, SelectionCriteria
            values @CartID, @tnNumOfRecords, @tcCreditCategory, @tcDescription, @tcSelectionCriteria

Does END means an early exit point from the procedure?