>Can you post your final code?
>
>-Mike

-- =============================================
-- Create procedure with OUTPUT Parameters
-- =============================================
-- creating the store procedure
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),
        @tnCardID int OUTPUT)
AS 
        
/*
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

 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 CartCDetail 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
*/

/* 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 @@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, @Err int, @CartContentID int

-- PK in the Cart table
SET @CartID = 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 @CartID = CartID, 
   	LastModTs = CURRENT_TIMESTAMP   
   	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) values (@tcUserID)
         SELECT @Err = @@ERROR, @CartID = 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 (@CartID, @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

-- Will have to also parse list of codes and insert into CartDetail table

_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' 	,
        'B' ,
	'some  crit' ,
        '1,2,3' ,
        @tnCartID OUTPUT

print @tnCartID
print @RetStat
GO

But it prints only 0 and doesn't print CartID. Why? Does print only prints the last command?