USE [Arizona];
BEGIN TRANSACTION;
SET XACT_ABORT ON;

DECLARE
    @mode                 INT = 2 /* 1 = SELECT / 2 = UPDATE */
   ,@cvRecordIDLowerLimit INT;

BEGIN TRY
    SELECT @cvRecordIDLowerLimit = [bapd].[BAPD_value]
      FROM [dbo].[Bmc_application_key]     [bapk] (NOLOCK)
      JOIN [dbo].[Bmc_application_default] [bapd] (NOLOCK)
          ON [bapd].[BAPD_bmc_application_key] = [bapk].[Bmc_application_key_ID]
     WHERE [bapk].[BAPK_key] = 'cvRecordIDLowerLimit';
    CREATE TABLE [#temp_address] (
        [Address_ID]         INT
       ,[AD_last_name]       VARCHAR(30)
       ,[ad_first_name]      VARCHAR(30)
       ,[ad_name]            VARCHAR(60)
       ,[ad_name_supplement] VARCHAR(255)
       ,[ad_vguid]           UNIQUEIDENTIFIER
    );

    INSERT INTO [#temp_address] (
        [Address_ID]
       ,[AD_last_name]
       ,[ad_first_name]
       ,[ad_name]
       ,[ad_name_supplement]
       ,[ad_vguid]
    )
    SELECT
        [ad].[Address_ID]
       ,[ad].[AD_last_name]
       ,[ad].[AD_first_name]
       ,[ad].[AD_name]
       ,[ad].[AD_name_supplement]
       ,[ad].[AD_VGUID]
      FROM [Arizona].[dbo].[Customer] [cust] (NOLOCK)
      JOIN [Arizona].[dbo].[Address]  [ad] (NOLOCK)
          ON [ad].[Address_ID]  = [cust].[CUST_address]
          AND [ad].[Address_ID] >= @cvRecordIDLowerLimit;

    UPDATE [t1]
       SET
        [t1].[AD_last_name] = [t2].[AD_last_name]
       ,[t1].[ad_name] = LEFT(ISNULL([t2].[AD_last_name], '') + ' ' + ISNULL([t1].[ad_first_name], ''), 60)
      FROM [#temp_address] [t1] (NOLOCK)
      JOIN [#temp_address] [t2] (NOLOCK)
          ON [t2].[Address_ID] = (
              SELECT TOP 1
                     [t3].[Address_ID]
                FROM [#temp_address] [t3] (NOLOCK)
               WHERE [t3].[Address_ID] > [t1].[Address_ID]
               ORDER BY NEWID()
          );

    IF @mode = 2
    BEGIN
        UPDATE [ad]
           SET
            [ad].[AD_last_name] = [t1].[AD_last_name]
           ,[ad].[AD_name] = [t1].[ad_name]
          FROM [#temp_address] [t1] (NOLOCK)
          JOIN [dbo].[Address] [ad] (NOLOCK)
              ON [ad].[Address_ID] = [t1].[Address_ID]
         WHERE ISNULL([ad].[AD_last_name], '') <> ISNULL([t1].[AD_last_name], '')
         OR ISNULL([ad].[AD_name], '')         <> ISNULL([t1].[ad_name], '');

    END
    ELSE
    BEGIN
        SELECT --top 100 
            [ad].[AD_last_name]
           ,'-->'
           ,[t1].[AD_last_name]
           ,[ad].[AD_name]
           ,'-->'
           ,[t1].[ad_name]
          FROM [#temp_address] [t1] (NOLOCK)
          JOIN [dbo].[Address] [ad] (NOLOCK)
              ON [ad].[Address_ID] = [t1].[Address_ID]
         WHERE ISNULL([ad].[AD_last_name], '') <> ISNULL([t1].[AD_last_name], '')
         OR ISNULL([ad].[AD_name], '')         <> ISNULL([t1].[ad_name], '');

    END; /* @mode = 2 */
    DROP TABLE [#temp_address];
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
    PRINT 'on line '+CAST(ERROR_LINE() AS VARCHAR(6))
    ROLLBACK TRANSACTION 
END CATCH