Monday, March 12, 2012

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)

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

Damianso, what did you modify?|||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--|||what is the value of your @.ID1 through @.ID9? if they don't contain any valid ContactID, - the delete will not occur.|||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

No comments:

Post a Comment