I guess this is why extreem programming advocates pair programing. With the input of several people you get a better routine than one person would write them selves.

BOb


>>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?