SQL Function – Get Current Person ID

The platform our church uses to help us manage information and relationships has a great feature to merge duplicate records. Unfortunately, this feature causes a new ID to be generated for a person. That can make is difficult to integrate with other custom features, systems, etc. I wrote this simple function to keep person links between integrated systems no matter what.

How To USE. 

Add [dbo].[cust_fn_getcurrent_person_id](YourPersonIDInCustTable) in your join clause.  Comment if you would like more explanation on how to use.

Function Code

USE [YourDataBase]
GO
/****** Object: UserDefinedFunction [dbo].[cust_fn_getcurrent_person_id] Script Date: 11/1/2014 9:21:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[cust_fn_getcurrent_person_id](@CCPID int)
RETURNS varchar(50)
AS
BEGIN

DECLARE @PersonID as int
SET @PersonID =

(Select Top 1 lt.current_person_id From
(SELECT cpm.new_person_id AS current_person_id, cpm.old_person_id AS old_person_id
FROM core_person_merged cpm INNER JOIN
core_person cp ON cpm.new_person_id = cp.person_id
UNION
SELECT cp2.person_id AS current_person_id, cp2.person_id AS old_person_id
FROM core_person cp2) LT
Where lt.old_person_id = @CCPID)

RETURN @PersonID
END

 

Leave a Reply

Your email address will not be published. Required fields are marked *