Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    23

    Unanswered: I think I broke a stored procedure

    There is a part of our internal web page that merges duplicates together I am building version 2.0 the original developer is gone and I am not experienced enough to figure this one out. Everything works fine exept that it does not delete the merged contact I think that the problem is in the stored procedure cause it is the only place I see a delete contactID at (At the very end)
    Code:
    CREATE PROCEDURE dbo.usp_contact_merge 
    (
    	@ID int,
    	@ID1 int =NULL,
    	@ID2 int =NULL,
    	@ID3 int =NULL,
    	@ID4 int =NULL,
    	@ID5 int =NULL,
    	@ID6 int =NULL,
    	@ID7 int =NULL,
    	@ID8 int =NULL,
    	@ID9 int =NULL,
    	@LastName varchar(50) =NULL, 
    	@FirstName varchar(50) =NULL, 
    	@Email  varchar(50)= NULL, 
    	@Address varchar(50) =NULL, 
    	@City varchar(50) =NULL, 
    	@State varchar(50) =NULL, 
    	@Zip varchar(50) =NULL, 
    	@Country varchar(50) =NULL, 
    	@HomePhone varchar(50) =NULL, 
    	@ReferredBy varchar(50) =NULL, 
    	@UpdatedBy varchar(50) =NULL
    )
    
    AS
    DECLARE
    @Count int,
    @ContactID int,
    @Spouse varchar(50),
    @Middle varchar(50),
    @Company varchar(100),
    @Title varchar(50),
    //Deleted variables for brevity
    DECLARE contact_cursor CURSOR FOR 
    SELECT 
    ContactID,
    FirstName,
    Spouse,
    LastName,
    Middle,
    Email,
    Company,
    Title,
    Address,
    Address1,
    Address2,
    City,
    State,
    Zip,
    HomePhone,
    WorkPhone,
    MobilePhone,
    UserID,
    LocationID,
    Email2,
    DateEntered,
    EnteredBy,
    Country,
    ReferredBy,
    Fax,
    Salutation,
    LetterDate,
    Store,
    PhoneExt,
    LastResults,
    TypeRV,
    InterestedIN,
    User3,
    User4,
    User5,
    Salesperson,
    Origin,
    User1,
    User2,
    User6,
    User8,
    User9,
    IDStatus,
    CID,
    Stock,
    Model,
    YR,
    ModelNo,
    Body,
    VIN,
    County,
    SOLD,
    PriceRange,
    Financing,
    TradeIn,
    HisBirthday,
    HerBirthday,
    Anniversary
    FROM Contacts
    WHERE ContactID = @ID
    OR ContactID = @ID1
    OR ContactID = @ID2
    OR ContactID = @ID3
    OR ContactID = @ID4
    OR ContactID = @ID5
    OR ContactID = @ID6
    OR ContactID = @ID7
    OR ContactID = @ID8
    OR ContactID = @ID9
    ORDER BY Timestmp DESC
    
    SET @Count = 0
    
    SET @MergeDate = GETDATE()
    SET @LastUpdated = GETDATE()
    
    OPEN contact_cursor
    
    FETCH NEXT FROM contact_cursor
    INTO 
    @ContactID1,
    @FirstName1,
    @Spouse1,
    @LastName1,
    @Middle1,
    @Email1,
    @Company1,
    @Title1,
    @Address1a,
    @Address11,
    @Address21,
    @City1,
    @State1,
    @Zip1,
    @HomePhone1,
    @WorkPhone1,
    @MobilePhone1,
    @UserID1,
    @LocationID1,
    @Email21,
    @DateEntered1,
    @EnteredBy1,
    @Country1,
    @ReferredBy1,
    @Fax1,
    @Salutation1,
    @LetterDate1,
    @Store1,
    @PhoneExt1,
    @LastResults1,
    @TypeRV1,
    @InterestedIN1,
    @User31,
    @User41,
    @User51,
    @Salesperson1,
    @Origin1,
    @User1a,
    @User21,
    @User61,
    @User81,
    @User91,
    @IDStatus1,
    @CID1,
    @Stock1,
    @Model1,
    @YR1,
    @ModelNo1,
    @Body1,
    @VIN1,
    @County1,
    @SOLD1,
    @PriceRange1,
    @Financing1,
    @TradeIn1,
    @HisBirthday1,
    @HerBirthday1,
    @Anniversary1
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	IF @DateEntered1 IS NOT NULL
    		BEGIN
    			SET @DateEntered = @DateEntered1
    		END
    	IF @EnteredBy1 IS NOT NULL
    		BEGIN
    			SET @EnteredBy = @EnteredBy1
    		END 
    	IF @FirstName IS NULL 
    		BEGIN
    			SET @FirstName = @FirstName1
    		END
    	IF @Spouse IS NULL 
    		BEGIN
    			SET @Spouse = @Spouse1
    		END
    	IF @LastName IS NULL 
    		BEGIN
    			SET @LastName = @LastName1
    		END		
    	IF @Middle IS NULL 
    		BEGIN
    			SET @Middle = @Middle1
    		END	
    	IF @Email IS NULL 
    		BEGIN
    			SET @Email = @Email1
    		END	
    	IF @Company IS NULL 
    		BEGIN
    			SET @Company = @Company1
    		END	
    	IF @Title IS NULL 
    		BEGIN
    			SET @Title = @Title1
    		END	
    	IF @Address IS NULL 
    		BEGIN
    			SET @Address = @Address1a
    		END	
    	IF @Address1 IS NULL 
    		BEGIN
    			SET @Address1 = @Address11
    		END	
    	IF @Address2 IS NULL 
    		BEGIN
    			SET @Address2 = @Address21
    		END	
    	IF @City IS NULL 
    		BEGIN
    			SET @City = @City1
    		END	
    	IF @State IS NULL 
    		BEGIN
    			SET @State = @State1
    		END	
    	IF @Zip IS NULL 
    		BEGIN
    			SET @Zip = @Zip1
    		END	
    	IF @HomePhone IS NULL 
    		BEGIN
    			SET @HomePhone = @HomePhone1
    		END	
    	IF @WorkPhone IS NULL 
    		BEGIN
    			SET @WorkPhone = @WorkPhone1
    		END	
    	IF @MobilePhone IS NULL 
    		BEGIN
    			SET @MobilePhone = @MobilePhone1
    		END	
    	IF @UserID IS NULL 
    		BEGIN
    			SET @UserID = @UserID1
    		END	
    	IF @LocationID IS NULL 
    		BEGIN
    			SET @LocationID = @LocationID1
    		END	
    	IF @Email2 IS NULL 
    		BEGIN
    			SET @Email2 = @Email21
    		END	
    	IF @Country IS NULL 
    		BEGIN
    			SET @Country = @Country1
    		END	
    	IF @ReferredBy IS NULL 
    		BEGIN
    			SET @ReferredBy = @ReferredBy1
    		END	
    	IF @Fax IS NULL 
    		BEGIN
    			SET @Fax = @Fax1
    		END	
    	IF @Salutation IS NULL 
    		BEGIN
    			SET @Salutation = @Salutation1
    		END	
    	IF @LetterDate IS NULL 
    		BEGIN
    			SET @LetterDate = @LetterDate1
    		END	
    	IF @Store IS NULL 
    		BEGIN
    			SET @Store = @Store1
    		END	
    	IF @PhoneExt IS NULL 
    		BEGIN
    			SET @PhoneExt = @PhoneExt1
    		END	
    	IF @LastResults IS NULL 
    		BEGIN
    			SET @LastResults = @LastResults1
    		END	
    	IF @TypeRV IS NULL 
    		BEGIN
    			SET @TypeRV = @TypeRV1
    		END	
    	IF @InterestedIN IS NULL 
    		BEGIN
    			SET @InterestedIN = @InterestedIN1
    		END	
    	IF @User3 IS NULL 
    		BEGIN
    			SET @User3 = @User31
    		END	
    	IF @User4 IS NULL 
    		BEGIN
    			SET @User4 = @User41
    		END				
    	IF @User5 IS NULL 
    		BEGIN
    			SET @User5 = @User51
    		END	
    	IF @Salesperson IS NULL 
    		BEGIN
    			SET @Salesperson = @Salesperson1
    		END	
    	IF @Origin IS NULL 
    		BEGIN
    			SET @Origin = @Origin1
    		END	
    	IF @User1 IS NULL 
    		BEGIN
    			SET @User1 = @User1a
    		END	
    	IF @User2 IS NULL 
    		BEGIN
    			SET @User2 = @User21
    		END	
    	IF @User6 IS NULL 
    		BEGIN
    			SET @User6 = @User61
    		END	
    	IF @User8 IS NULL 
    		BEGIN
    			SET @User8 = @User81
    		END	
    	IF @User9 IS NULL 
    		BEGIN
    			SET @User9 = @User91
    		END	
    	IF @Stock IS NULL 
    		BEGIN
    			SET @Stock = @Stock1
    		END	
    	IF @Model IS NULL 
    		BEGIN
    			SET @Model = @Model1
    		END	
    	IF @YR IS NULL 
    		BEGIN
    			SET @YR = @YR1
    		END	
    	IF @ModelNo IS NULL 
    		BEGIN
    			SET @ModelNo = @ModelNo1
    		END	
    	IF @Body IS NULL 
    		BEGIN
    			SET @Body = @Body1
    
    		END	
    	IF @VIN IS NULL 
    		BEGIN
    			SET @VIN = @VIN1
    		END	
    	IF @County IS NULL 
    		BEGIN
    			SET @County = @County1
    		END	
    	IF @SOLD IS NULL 
    		BEGIN
    			SET @SOLD = @SOLD1
    		END	
    	IF @PriceRange IS NULL 
    		BEGIN
    			SET @PriceRange = @PriceRange1
    		END	
    	IF @Financing IS NULL 
    		BEGIN
    			SET @Financing = @Financing1
    		END	
    	IF @TradeIn IS NULL 
    		BEGIN
    			SET @TradeIn = @TradeIn1
    		END	
    	IF @HisBirthday IS NULL 
    		BEGIN
    			SET @HisBirthday = @HisBirthday1
    		END	
    	IF @HerBirthday IS NULL 
    		BEGIN
    			SET @HerBirthday = @HerBirthday1
    		END	
    	IF @Anniversary IS NULL 
    		BEGIN
    			SET @Anniversary = @Anniversary1
    		END	
    	SET @Count = @Count + 1
    	IF @ContactID1 <> @ID
    		BEGIN
    			EXEC usp_contact_merge_delete @ID, @ContactID1
    		END
    	FETCH NEXT FROM contact_cursor
    	INTO 
    	@ContactID1,
    	@FirstName1,
    	@Spouse1,
    	@LastName1,
    	@Middle1,
    	@Email1,
    	@Company1,
    	@Title1,
    	@Address1a,
    	@Address11,
    	@Address21,
    	@City1,
    	@State1,
    	@Zip1,
    	@HomePhone1,
    	@WorkPhone1,
    	@MobilePhone1,
    	@UserID1,
    	@LocationID1,
    	@Email21,
    	@DateEntered1,
    	@EnteredBy1,
    	@Country1,
    	@ReferredBy1,
    	@Fax1,
    	@Salutation1,
    	@LetterDate1,
    	@Store1,
    	@PhoneExt1,
    	@LastResults1,
    	@TypeRV1,
    	@InterestedIN1,
    	@User31,
    	@User41,
    	@User51,
    	@Salesperson1,
    	@Origin1,
    	@User1a,
    	@User21,
    	@User61,
    	@User81,
    	@User91,
    	@IDStatus1,
    	@CID1,
    	@Stock1,
    	@Model1,
    	@YR1,
    	@ModelNo1,
    	@Body1,
    	@VIN1,
    	@County1,
    	@SOLD1,
    	@PriceRange1,
    	@Financing1,
    	@TradeIn1,
    	@HisBirthday1,
    	@HerBirthday1,
    	@Anniversary1
    
    END
    
    CLOSE contact_cursor
    DEALLOCATE contact_cursor
    
    
    UPDATE Contacts SET
    FirstName = @FirstName,
    Spouse = @Spouse,
    LastName = @LastName,
    Middle = @Middle,
    Email = @Email,
    Company = @Company,
    Title = @Title,
    Address = @Address,
    Address1 = @Address1,
    Address2 = @Address2,
    City = @City,
    State = @State,
    Zip = @Zip,
    HomePhone = @HomePhone,
    WorkPhone = @WorkPhone,
    MobilePhone = @MobilePhone,
    UserID = @UserID,
    LocationID = @LocationID,
    Email2 = @Email2,
    DateEntered = @DateEntered,
    EnteredBy = @EnteredBy,
    LastUpdated = @LastUpdated,
    UpdatedBy = @UpdatedBy,
    Country = @Country,
    ReferredBy = @ReferredBy,
    Fax = @Fax,
    Salutation = @Salutation,
    LetterDate = @LetterDate,
    Store = @Store,
    PhoneExt = @PhoneExt,
    LastResults = @LastResults,
    TypeRV = @TypeRV,
    InterestedIN = @InterestedIN,
    User3 = @User3,
    User4 = @User4,
    User5 = @User5,
    MergeDate = @MergeDate,
    Salesperson = @Salesperson,
    Origin = @Origin,
    User1 = @User1,
    User2 = @User2,
    User6 = @User6,
    User8 = @User8,
    User9 = @User9,
    IDStatus = @IDStatus,
    CID = @CID,
    Stock = @Stock,
    Model = @Model,
    YR = @YR,
    ModelNo = @ModelNo,
    Body = @Body,
    VIN = @VIN,
    County = @County,
    SOLD = @SOLD,
    PriceRange = @PriceRange,
    Financing = @Financing,
    TradeIn = @TradeIn,
    HisBirthday = @HisBirthday,
    HerBirthday = @HerBirthday,
    Anniversary = @Anniversary,
    LM = 0 
    WHERE ContactID = @ID
    
    DELETE FROM Contacts 
    WHERE ContactID = @ID1
    OR ContactID = @ID2
    OR ContactID = @ID3
    OR ContactID = @ID4
    OR ContactID = @ID5
    OR ContactID = @ID6
    OR ContactID = @ID7
    OR ContactID = @ID8
    OR ContactID = @ID9
    
    RETURN
    GO

    I pass all the variables in through asp
    with statements like

    .Parameters.Append .CreateParameter("@RETURN_VALUE",adInteger, adParamReturnValue, 0)
    .Parameters.Append .CreateParameter("@ID",adInteger, adParamInput, 0, sid)


    Any ideas.. need more info?

    Thanks

    Damian
    "Everything is possible, somethings are just less likely then others"

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    so, what did you modify?

  3. #3
    Join Date
    Jul 2003
    Posts
    23

    Red face

    I don't know.... I did'nt think that I did anything to this procedure or anything to this part of the program.

    Thinking hard...

    D--
    "Everything is possible, somethings are just less likely then others"

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    what is the value of your @ID1 through @ID9? if they don't contain any valid ContactID, - the delete will not occur.

  5. #5
    Join Date
    Jul 2003
    Posts
    23
    response.write the information right before the stored procedure call is as follows.

    ID1 = 210494 'ID to delete
    sid = 207298 'current ID

    ID2 - 9 not set
    "Everything is possible, somethings are just less likely then others"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •