Tracing Merged Records in PE from old (merged) account to new account

--this function takes an int input of the possibly no-longer-existing account, and returns the account number (cltcode) of the current account



CREATE FUNCTION dbo.moa_merge_trace (
@old int
) RETURNS int
as begin
declare @new int, @count int
set @count = 0 -- to prevent infinite loops
--begin recursive trace
if exists (select cltcode from clients with(nolock) where cltcode = @old) return @old

while exists (SELECT FromID FROM PATRONEDGE_MERGELOG with(nolock) where FromID = @old and metaobject_id = 713) and @count < 100
begin
set @new = (SELECT ToID FROM PATRONEDGE_MERGELOG with(nolock) where FromID = @old and metaobject_id = 713)
set @old = @new
set @count = @count + 1
end

return @new
end