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:     02/28/2003
PURPOSE:        This procedure creates a Shopping Cart Info for the given user
                It creates new CartID in Cart table, if it doesn't exist yet 
                Otherwise updates ModiDate field with current time
                Inserts info into CartContent and CartDetail tables  

INPUTS:         @tcUserID  varchar(20) - UserID 
                @tnNumOfRecords int - Number of selected child records
                @tcDescription text - "Bundle" description
                @tcCreditCategory char(1) - B/F/L (Bankruptcy/Foreclosure/Liens) 
	        @tcSelectionCriteria text - User-defined criteria from the Search page
                @tcListOfCodes varchar(500) - comma-delimited list of CredIDs

OUTPUTS:        
    ( RETURN CODES ) 
                                0 - Successful
                                1 - UserID is NULL                                   
                                2 - Tables do not exist
                                Actual error code for updates/insert
                                
                                
                                @tnCartID int - CartID for this User
                                
USAGE EXAMPLE:
    declare @tnCartID int, @RetStat int 

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


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

UPDATES (date,name,details): 
    
********************************************************************************** */


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 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 'Cart ID =' + RTRIM(CONVERT(varchar(30), @tnCartID)) + ' Error code = ' + RTRIM(CONVERT(varchar(30), @RetStat))
GO