Shane you're right.
What I need to do is check @hierarchyflag for each row (for each profile) and for some reason I keep getting the @hierarchyflag value of the first returned row. I'm setting the value of @hierarchyflag inside the while, so it should be different each time, since I get it from the @profileName. In my understanding, if @hierarchyflag for Profile 1 is 1 and @hierarchyflag for Profile 2 is 2, shouldn't this print out 
'aaa'
'bbb'
Why am I getting
'aaa'
'aaa'?


DECLARE @profileName nvarchar(200);
DECLARE @hierarchyFlag tinyint;
DECLARE @db_cursor CURSOR;
DECLARE @ProfileNameTable TABLE (ProfileName nvarchar(100));
INSERT INTO @ProfileNameTable (ProfileName) VALUES ('Profile 1'), ('Profile 2');

SET @db_cursor = CURSOR FOR
SELECT ProfileName FROM @ProfileNameTable

OPEN @db_cursor;
FETCH NEXT FROM @db_cursor INTO @profileName;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @hierarchyFlag = (SELECT TOP 1 HierarchyFlag FROM [dbo].[vSB_IdM_Hibis_Profile] WHERE ltrim(rtrim(HibisProfileName)) = @profileName);

 IF (@hierarchyFlag = 1)
        BEGIN
        PRINT 'aaa'
        END
 ELSE IF (@hierarchyFlag = 2)
        BEGIN
        PRINT 'bbb'
        END
 FETCH NEXT FROM @db_cursor INTO @profileName
END
CLOSE @db_cursor;
DEALLOCATE @db_cursor;