CREATE PROC spCheckForCustomerMatch
(@SourceID INT,
@FirstName NVARCHAR(255),
@LastName NVARCHAR(255),
@Phone NVARCHAR(20),
@PhoneAreaCode NVARCHAR(20))
AS
SELECT MatchType, ID FROM
(SELECT MatchType
= CASE
WHEN
(FirstName = @FirstName AND
LastName = @LastName AND
Phone = @Phone AND
PhoneAreaCode = @PhoneAreaCode AND
ID <> @SourceID)
THEN
1
WHEN
(FirstName = @FirstName AND
LastName = @LastName AND
ID <> @SourceID)
THEN
2
END,
ID FROM APTIFY..vwCustomers) CustomerMatch
WHERE MatchType IS NOT NULL
OLD -- CREATE PROC spCheckForCustomerMatch
(@SourceID INT,
@FirstName NVARCHAR(255),
@LastName NVARCHAR(255))
AS
SELECT MatchType, ID FROM
(SELECT MatchType
= CASE
WHEN
(FirstName = @FirstName AND
LastName = @LastName AND
ID <> @SourceID)
THEN
2
END,
ID FROM APTIFY..vwCustomers) CustomerMatch
WHERE MatchType IS NOT NULL |