Is it possible to put a CASE expression inside a While loop on SQL Server 2008?

DECLARE @FirstName NVARCHAR(50),
        @MiddleName NVARCHAR(50),
        @LastName NVARCHAR(50),
        @Email NVARCHAR(50),
        @EmployeeID INT,
        @FN NVARCHAR(MAX),
        @MN NVARCHAR(MAX),
        @LN NVARCHAR(MAX)

DECLARE cur CURSOR FOR
    SELECT    FirstName
                , MiddleName
                , LastName
                , Username
    FROM    temp

    OPEN cur
        FETCH NEXT FROM cur
        INTO @FirstName, @MiddleName, @LastName, @Email

        WHILE @@FETCH_STATUS = 0
        BEGIN

            DECLARE cur2 CURSOR FOR
                SELECT    EmployeeID
                        , FirstName
                        , MiddleName
                        , LastName
                FROM    HRIS_Employees

    开发者_JAVA技巧            OPEN cur2
                    FETCH NEXT FROM cur2
                    INTO @EmployeeID, @FN, @MN, @LN

                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                        CASE 
                            WHEN ((@FN = @FirstName 
                            AND @MN = @MiddleName))
                            AND @LN = @LastName) THEN

                            UPDATE  HRIS_EmployeeContacts
                            SET     Email = @Email
                            WHERE   HRIS_EmployeeContacts.EmployeeID = @EmployeeID
                        END

                        FETCH NEXT FROM cur2
                        INTO @EmployeeID, @FN, @MN, @LN

                    END
                CLOSE cur2
                DEALLOCATE cur2

            FETCH NEXT FROM cur
            INTO @FirstName, @MiddleName, @LastName, @Email;

        END 

    CLOSE cur
DEALLOCATE cur

An error occurred that says "Incorrect syntax near the keyword 'CASE'."

---

Why is this a cursor at all? You can replace all that pasta with a single UPDATE statement:

UPDATE e
    SET e.Email = t.Username
    FROM dbo.HRIS_EmployeeContacts AS e
    INNER JOIN dbo.temp AS t
    ON t.FirstName = e.FirstName
    AND t.MiddleName = e.MiddleName
    AND t.LastName = e.LastName;

---

CASE is not a control flow statement. It's an operator. You want IF - ELSE.

---

I think you are using CASE where you really need an IF. CASE can only be used in an expression while IF is a flow control keyword.

Replace this:

CASE 
 WHEN ((@FN = @FirstName AND @MN = @MiddleName)) AND @LN = @LastName) THEN

 UPDATE  HRIS_EmployeeContacts
 SET     Email = @Email
 WHERE   HRIS_EmployeeContacts.EmployeeID = @EmployeeID
END

with this:

IF ((@FN = @FirstName AND @MN = @MiddleName)) AND @LN = @LastName)
  BEGIN
    UPDATE  HRIS_EmployeeContacts
    SET     Email = @Email
    WHERE   HRIS_EmployeeContacts.EmployeeID = @EmployeeID
  END

Also, this cursor inside a cursor against a temp table is somewhat of a monstrosity. You might consider reworking this to not use cursors at all.

---

You don't need a case statement, and IF would work

IF (((@FN = @FirstName AND @MN = @MiddleName)) AND @LN = @LastName) 
    BEGIN
       UPDATE   HRIS_EmployeeContacts
       SET      Email = @Email
       WHERE    HRIS_EmployeeContacts.EmployeeID = @EmployeeID
    END