title: What am I doing wrong with the loop use in SQL
body: <p>I need to compare emails from tables and if they match I need to show some data from the third table and I'm trying to do that through a while loop which goes through the string of all emails and compares them and if its a match it should get the data from third table</p>
  
  <p>Im pasting the query from pastebin here: <a href="https://pastebin.com/zyjcJngf" rel="nofollow noreferrer">https://pastebin.com/zyjcJngf</a></p>
  
  <p>The part where I want to use the loop</p>
  
  <pre><code>select distinct Email,
  CASE dbo.spValueToString((SELECT COUNT(*) 
      FROM tblEmailBlackList WHERE tblEmailBlackList.Email=LiveCampaign_SubscriberList_Email.Email AND tblEmailBlackList.PortalID&gt;=-1))
  WHEN 'Da' THEN
  
      WHILE @vsi &gt; 1
      BEGIN
          SET @testEmail = (SELECT LEFT(@resultCpy, CHARINDEX(',', @resultCpy) - 1)) --dobi prvi mail
          SET @testEmail = (SELECT REPLACE(@testEmail, ' ', '')) --zbriše vse ' ' če obstajajo
          SET @resultCpy = (SELECT SUBSTRING(@resultCpy, LEN(@testEmail) + 2, LEN(@resultCpy))) --odstani prvi mail
          IF      
              @testEmail = Email
              BREAK
          ELSE
              SET @vsi = @vsi - 1
      END
      SET @vsi = (SELECT LEN(@result) - LEN(REPLACE(@result, ',', '')) + 1)
      SET @resultCpy = @result
  
      (SELECT DISTINCT PortalLocalization.PortalName
      FROM tblEmailBlackList
      LEFT JOIN tblLiveCampaignSettings ON tblLiveCampaignSettings.ModuleID = tblEmailBlackList.ModuleID
      LEFT JOIN PortalLocalization ON PortalLocalization.PortalID = tblEmailBlackList.PortalID AND PortalLocalization.CultureCode = 'sl-SI'
      WHERE Email = @testEmail
      AND tblEmailBlackList.PortalID &gt;= 0
      UNION
      SELECT DISTINCT PortalLocalization.PortalName
      FROM tblEmailBlackList
      LEFT JOIN vw_TabModules ON vw_TabModules.ModuleID = tblEmailBlackList.ModuleID
      LEFT JOIN PortalLocalization ON PortalLocalization.PortalID = vw_TabModules.PortalID AND PortalLocalization.CultureCode = 'sl-SI'
      WHERE Email = @testEmail
      AND tblEmailBlackList.PortalID = -1)    
  ELSE 'Ne' END
  AS LocalBlockList
  FROM LiveCampaign_SubscriberList_Email
  </code></pre>
  
  <p>The problem I have here is that I get an error when I add the loop in the code, but if I run the loop alone it works. I get the incorrect syntax near WHILE and The multi-part identifier "LiveCampaign_SubscriberList_Email.Email" could not be bound.</p>