问SQL Server游标-无法正确分配变量

EN

- exec dbo.sp_UserMigration_Users_Copy
    DROP PROCEDURE IF EXISTS dbo.sp_UserMigration_Users_Copy
    GO

    ;USE [MyDB];
    GO

    CREATE  PROCEDURE dbo.sp_UserMigration_Users_Copy
     @UserID UNIQUEIDENTIFIER = NULL
     --,@ContactID INT

    AS 

    DECLARE @Email nvarchar(100),
            @ContactID INT;


    DECLARE @SysID INT
    SET @SysId = 17511; 

    CREATE TABLE #T 
    (
    ID INT IDENTITY(1,1) NOT NULL,
     UserID NVARCHAR(50)NOT NULL,
     Email NVARCHAR(50) NOT NULL 
    );
    INSERT INTO #T 
    SELECT * 
    FROM MyDB.dbo.User
    WHERE UserID IN (   
                            '0604C514',
                            'C1FDAF34', 
                            '23BABE2D',
                            'EBA21D10'
                        ); 

    IF NOT EXISTS (select * from MyDB.sys.tables where name = N'UserIDContactIDMigrationLog')

        CREATE TABLE MyDB.dbo.UserIDContactIDMigrationLog
            (
                UserID UNIQUEIDENTIFIER /* CONSTRAINT [PK_UserIDContactIDMigrationLog_UserID] PRIMARY KEY */ NOT NULL,  -- somehow inserts duplicate UserIDs 
                ContactId INT /*UNIQUE CONSTRAINT [UNIQUE_ContactId] */ NOT NULL,
                CreatedDt DATETIME2 NULL,
                UpdatedDt DATETIME2 NULL,
                UpdatedFlag BIT /*CONSTRAINT [DF_MigratedFlag] DEFAULT(0) */ NULL
            );


    DECLARE @COUNT INT 
    DECLARE @I INT = 1
    DECLARE @UserID NVARCHAR(50) ;
    DECLARE @Email NVARCHAR(50)
    SELECT @COUNT = COUNT(*) FROM #T
    WHILE(@I <= @COUNT)

    BEGIN 
            SELECT @UserID = UserID, @Email= LTRIM(RTRIM(Email)) FROM #T WHERE ID = @I   


        SELECT @ContactID = (
            SELECT c.ContactId
            FROM MyDB.dbo.Contact c 
            LEFT OUTER JOIN MyDB.dbo.Login l ON c.ContactId = l.ContactId  
            WHERE (LTRIM(RTRIM(c.ContactEmailTx)) = @Email 
                    OR LTRIM(RTRIM(l.ContactLoginNameTx)) = @Email 
                    --OR l.ContactLoginNameTx LIKE @Email+'%'  -- this concept does not work in case of leading space at the ContactLoginNameTx so need to use the code below 
                    OR SUBSTRING(LTRIM(RTRIM(l.ContactLoginNameTx)), -1, CHARINDEX('.INACTIVE.', l.ContactLoginNameTx)) = @Email)
            AND (c.ContactEmailTx != '' OR c.ContactEmailTx IS NOT NULL OR l.ContactLoginNameTx != '' OR l.ContactLoginNameTx IS NOT NULL)
                            )

    -- check if a contact is new
        IF (@ContactID IS NULL)
        BEGIN
            -- -----------------------------------------------------
            -- MyDB.dbo.Contact
            -- -----------------------------------------------------
            INSERT INTO [MyDB].[dbo].[Contact]
                (   
                    [ContactFirstNameTx],
                    [ContactLastNameTx],
                    [ContactEmailTx],
                    [ContactTitleTx],
                    [ContactCreateDt],
                    [ContactCreateByID],
                    [ContactCreateLoginTypeID]
                )
            SELECT 
                    [FirstName],
                    [LastName],
                    [Email],
                    [Title],
                    --[AccountId],
                    [UserCreatedDate],
                    @SysId AS [ContactCreateByID],  -- as as flag for Onvia import[ContactCreateLoginTypeID]
                    -10 AS [ContactCreateLoginTypeID]
            FROM #T o WITH (NOLOCK) 
            WHERE UserID = @UserID 

            SET @ContactID = SCOPE_IDENTITY()
        END
    --   select * from  [MyDB].[dbo].[Contact] where contactid in ( 1051364, 466440, 560466, 618576)
        -- -----------------------------------------------------
        --MyDB.dbo.Login
        -- -----------------------------------------------------
        INSERT INTO [MyDB].[dbo].[Login]          
        (   
            ContactID,
            [ContactLoginNameTx], -- V.C. this is email. the field is has a unique constraint
            [ContactLoginActiveIn],
            [ContactLoginCreateDt],
            [ContactLoginCreateByID],
            [ContactLoginCreateLoginTypeID],
            [ContactLoginPasswordLastChangeDt],
            [ContactLoginLastLoginDt],
            [ContactLoginPasswordExclusionIn] -- this is not nullable and BIT
        )
        SELECT 
            @ContactID AS ContactID,
            [Email],
            CASE WHEN [UserStatusID] IN (1, 3) THEN 1 ELSE 0 END AS [ContactLoginActiveIn], -- v.c. in Onvia 1 is Active, 2 - Inactive, 3 - Invited, 4 - Inactive By System, 5 - Pending Registration
            [UserCreatedDate],
            @SysId AS ContactLoginCreateByID,  -- as as flag for Onvia import[ContactCreateLoginTypeID]
            -10 AS [ContactLoginCreateLoginTypeID],
            [UserLastPasswordChangedDate],
            [UserLastLoginDate],
            0 as ContactLoginPasswordExclusionIn -- this is not nullable and BIT
        FROM #T o WITH (NOLOCK) 
        WHERE UserID = @UserID
        AND NOT EXISTS (select * from MyDB.dbo.Login z where z.ContactID = @ContactID)

        -- -----------------------------------------------------
        -- Activate any inactive users 
        -- -----------------------------------------------------

        IF EXISTS (select * from MyDB.dbo.Login where ContactLoginActiveIn = 0 and ContactID = @ContactID)
            AND NOT EXISTS (select * from MyDB.dbo.Login where ContactLoginActiveIn = 1 and ContactID = @ContactID)
        BEGIN
            UPDATE [MyDB].[dbo].[Login]
            SET ContactLoginNameTx = @Email,
                ContactLoginActiveIn = (select case when [UserStatusID] IN (1, 3) then 1 else 0 end 
                                            from #T o where o.UserID = @UserID) 
            WHERE ContactID = @ContactID
        END

        -- -----------------------------------------------------
        --MyDB.dbo.ContactPhoneNumber
        -- -----------------------------------------------------
        INSERT INTO [MyDB].[dbo].[PhoneNumber]        
        (   
            ContactID,
            PhoneNumberTypeID,
            ContactPhoneNumberValueTx 
        )

        SELECT 
            @ContactID AS ContactID,
            1 AS PhoneNumberTypeID,
            ISNULL([PhoneNumber1],'') AS ContactPhoneNumberValueTx
        FROM #T o WITH (NOLOCK) 
        WHERE o.UserID = @UserID 
        AND NOT EXISTS (select * from [MyDB].[dbo].[PhoneNumber] z where z.ContactID = @ContactID);

        -- -----------------------------------------------------
        --MyDB.dbo.ContactOrg
        -- -----------------------------------------------------
        INSERT INTO [MyDB].[dbo].[Org]      
        (   
            ContactID,
            OrgID
        )
        SELECT 
            @ContactID AS ContactID,
            b.OrgID AS OrgID
        FROM #T o WITH (NOLOCK) 
        INNER JOIN ImportQueue.dbo.OnviaAccountIDOrgIDMigrationLog b WITH (NOLOCK) ON o.AccountId = b.OnviaAccountID
        WHERE o.UserID = @UserID 
        AND NOT EXISTS (select * from [MyDB].[dbo].[Org] z where z.ContactID = @ContactID);

        -- -----------------------------------------------------
        ---- MyDB.dbo.UserIDContactIDMigrationLog
        ---- log output for new users
        -- -----------------------------------------------------

        INSERT INTO MyDB.dbo.UserIDContactIDMigrationLog
        (
            UserID,
            ContactId,
            CreatedDt,
            UpdatedFlag
        )
        VALUES 
        (
            @UserID,
            @ContactID,
            GETDATE(),
            0 
        )

            -- -----------------------------------------------------
            --  EXISTING MATCHED USERS
            -- -----------------------------------------------------
                -- check if a contact is existing
        IF (@ContactID IS NOT NULL)
        BEGIN
            -- -----------------------------------------------------
            -- MyDB.dbo.Contact
            -- -----------------------------------------------------
            UPDATE MyDB.dbo.Contact
            SET ContactFirstNameTx =
                    CASE 
                        WHEN ContactFirstNameTx NOT IN ('', NULL) THEN c.FirstName
                        ELSE ContactFirstNameTx
                    END,
                ContactLastNameTx = 
                    CASE 
                        WHEN ContactLastNameTx IN ('', NULL) THEN c.LastName
                        ELSE ContactLastNameTx
                    END,
                ContactEmailTx = 
                    CASE 
                        WHEN ContactEmailTx IN ('', NULL) THEN c.Email
                        ELSE ContactEmailTx
                    END,
                ContactTitleTx =
                    CASE 
                        WHEN ContactTitleTx IN ('', NULL) THEN c.Title
                        ELSE ContactTitleTx
                    END,
                [ContactCreateDt] =
                    CASE 
                        WHEN ContactCreateDt IN ('', NULL) THEN c.UserCreatedDate
                        ELSE ContactCreateDt
                    END,
                [ContactModifyByID] = @SysId,
                [ContactModifyLoginTypeID] = - 10

            FROM #T c
                WHERE UserID = @UserID 
                AND ContactID = @ContactID;
        -- -----------------------------------------------------
        ---- MyDB.dbo.UserIDContactIDMigrationLog
        ---- log output for existing matched users
        -- -----------------------------------------------------

                INSERT INTO MyDB.dbo.UserIDContactIDMigrationLog 
                 (
                     UserID,
                     ContactId,
                     UpdatedDt,
                     UpdatedFlag
                 )

                VALUES 
                 (
                    @UserID,
                    @ContactID,
                    GETDATE(),
                    1 
                 );




        END
 SET @I = @I+1;
    END