SQL before / after

gistfile1.sql

/*
   / __ )/ ____/ ____/ __ \/ __ \/ ____/
  / __  / __/ / /_  / / / / /_/ / __/   
 / /_/ / /___/ __/ / /_/ / _, _/ /___   
/_____/_____/_/    \____/_/ |_/_____/   

*/


	IF @ParentUserID = @ChildUserID
	BEGIN  	
		SELECT -4 AS IsValid      
		RETURN
	END

	DECLARE @ChildUserGroupID INT
		,@ChildUserCurrencyIsoNumber SMALLINT
		,@ChildUserCountryCode VARCHAR(10)
		,@ParentUserGroupID INT
		,@ParentUserCurrencyIsoNumber SMALLINT
		,@ParentUserCountryCode VARCHAR(10)
		
	-- update fraud logs
	IF EXISTS (
		 SELECT *
		 FROM  frauds  WITH (NOLOCK)
		 WHERE author_id = @ChildUserID and whitelist=0
		)	
	BEGIN				
		EXEC spMergeAccountFraudLog_Increment @ParentUserID, @ChildUserID, 2
		SELECT -1 AS IsValid
		RETURN
	END

	IF EXISTS (					
		 SELECT *
		 FROM  frauds  WITH (NOLOCK)
		 WHERE author_id = @ParentUserID and whitelist=0
		)					
	BEGIN				
		EXEC spMergeAccountFraudLog_Increment @ParentUserID, @ChildUserID, 1
		SELECT -1 AS IsValid
		RETURN
	END

	-- select main user data
	SELECT 
		@ChildUserGroupID=Group_ID, 
		@ChildUserCurrencyIsoNumber = CurrencyIsoNumber,		
		@ChildUserCountryCode = Country_Code
	FROM
		users
	WHERE
		Author_ID = @ChildUserID		
		
	SELECT 
		@ParentUserGroupID=Group_ID, 
		@ParentUserCurrencyIsoNumber = CurrencyIsoNumber,		
		@ParentUserCountryCode = Country_Code
	FROM
		users
	WHERE
		Author_ID = @ParentUserID					

	-- check if at least one user is banned
	IF @ChildUserGroupID IN (2,14) OR @ParentUserGroupID IN (2,14)
	BEGIN
		SELECT -1 AS IsValid
		RETURN
	END

	-- check for curencies and cultures
	IF (@ParentUserCountryCode IS NOT NULL AND @ParentUserCountryCode<>@ChildUserCountryCode)
	OR (@ParentUserCurrencyIsoNumber IS NOT NULL AND @ParentUserCurrencyIsoNumber<>@ChildUserCurrencyIsoNumber)
	BEGIN
		SELECT -2 AS IsValid
		RETURN
	END

	-- child account is an expert one
	IF @ChildUserGroupID IN (4,5,6,8,9)
	BEGIN
		SELECT -3 AS IsValid
		RETURN
	END
	
	SELECT @ParentUserID AS IsValid

/*
  ___    ______________________ 
   /   |  / ____/_  __/ ____/ __ \
  / /| | / /_    / / / __/ / /_/ /
 / ___ |/ __/   / / / /___/ _, _/ 
/_/  |_/_/     /_/ /_____/_/ |_|  
*/

GO 
DECLARE @MergeResult INT;

DECLARE @MergeError_AreEqual               INT = -4
DECLARE @MergeError_ChildIsExpert          INT = -3
DECLARE @MergeError_AreDifferentCurrencies INT = -2
DECLARE @MergeError_IsBanned               INT = -1
DECLARE @MergeError_IsFraud                INT = -1

IF @ParentUserID = @ChildUserID 
BEGIN
 SELECT @MergeError_AreEqual AS IsValid
 RETURN 
END


DECLARE @FraudAccount            INT  = NULL
DECLARE @Currencies              INT  = 0
DECLARE @Countries               INT  = 0
DECLARE @GuestsOrBannedAccounts  INT  = 0
DECLARE @ChildExpertAccounts     INT  = 0 -- Rated from 2 to 500+	
		
SELECT @Currencies    = COUNT( DISTINCT u.CurrencyIsoNumber )
     , @Countries     = COUNT( DISTINCT u.Country_Code      )  

     , @GuestsOrBannedAccounts = COUNT( CASE WHEN u.Author_ID = @ChildUserID AND u.Group_ID IN (2, 14)     THEN 1 ELSE NULL END )
     , @ChildExpertAccounts    = COUNT( CASE WHEN u.Author_ID = @ChildUserID AND u.Group_ID IN (4,5,6,8,9) THEN 1 ELSE NULL END )
	 
     , @FraudAccount  =  MAX( CASE WHEN f.Author_ID = @ParentUserID THEN 1 --Parent 
	                           WHEN f.Author_ID = @ChildUserID  THEN 2 --Child
			           ELSE NULL END )
			                   
        FROM frauds    AS f WITH (NOLOCK) 
  RIGHT JOIN users     AS u WITH (NOLOCK) 
          ON u.author_id = f.author_id AND f.whitelist=1
       WHERE u.author_id IN (@ParentUserID, @ChildUserID)
 

SET @MergeResult = 
( CASE 
    WHEN @FraudAccount IS NOT NULL         THEN @MergeError_IsFraud
    WHEN @GuestsOrBannedAccounts > 0       THEN @MergeError_IsBanned
    WHEN @Currencies > 1 OR @Countries > 1 THEN @MergeError_AreDifferentCurrencies
    WHEN @ChildExpertAccounts > 0          THEN @MergeError_ChildIsExpert
  
    ELSE @ParentUserID END -- use @ParentUserId when no merge errors
)

IF @MergeResult = @MergeError_IsFraud
BEGIN
  EXEC spMergeAccountFraudLog_Increment @ParentUserID, @ChildUserID, @FraudAccount
END
	  
SELECT @MergeResult AS IsValid

RETURN