Stored Procedure - Script Help for Duplicate Checks
Posted on 2006-07-10
The previous DBA here had set up the stored procedure to check for duplicates, but the logic wasn't done correctly. Currently, the script looks for duplicates with same names and different addresses.
What we are trying to do is have the script to look up same last name and same first X number of characters at beginning of address field.
This is the stored procedure we have now:
CREATE PROCEDURE xp_DupCheck (
SELECT tA.id AS Address_id,tA.*,
tPV.phonenumber AS "VoicePhone", tPT.phonenumber AS "TTYPhone"
FROM tblAddress tA
LEFT JOIN tblPhone tPV ON (tPV.addressid = tA.id AND tPV.typeid = 4)
LEFT JOIN tblPhone tPT ON (tPT.addressid = tA.id AND tPV.typeid = 6)
WHERE ((first_name = @first_name AND last_name = @last_name AND last_name != '')
OR (last_name = '' AND company != '' AND company = @company))
AND ((city != '' AND city = @city AND state = @state)
OR (@zipcode != '' AND zipcode = @zipcode))
ORDER BY last_name, first_name, company, state, city
Can someone please help me with this stored procedure? Need to be able to query results with same last name and same address field (at least the first couple of characters in address field)
Thanks in advance!