title: use a "temporary table" and "while loop" together in sql
body: <p>I want to create a table and after that use the table information in a while loop. I had been used 'with' to create the table but my code has error. The code comes below:</p>
  
  <pre><code>declare @i integer
  set @i=0;
  
  with cte as
  (SELECT ROW_NUMBER()OVER(ORDER BY ItemID) as RowNumber,*,DATEDIFF(day,GETDATE(),AdDateTo)as DaysToExpire
  FROM KenticoCMS1.dbo.AD_Advertise inner join KenticoCMS1.dbo.CMS_User 
      ON KenticoCMS1.dbo.AD_Advertise.ItemCreatedBy = KenticoCMS1.dbo.CMS_User.UserID
  WHERE DATEDIFF(day,GETDATE(),AdDateTo) in (SELECT RemainDays FROM KenticoCMS1.dbo.AD_SendEmailForExpire)
      AND AdShow=1)
  --SELECT * FROM cte 
  
  while (  @i&lt;=(select max(RowNumber) from cte))
  BEGIN   
          @i=@i+1;
      EXEC msdb.dbo.sp_send_dbmail @profile_name='ExpireAdvertiseEmail',
      @recipients= select Email FROM cte where RowNumber=@i , --'mj.yazdani1988@gmail.com',
      @subject='Test message',
      @body='This is the body of the test message.Congrates Database Mail Received By you Successfully.'      
  END
  GO
  </code></pre>
  
  <p>and my error: </p>
  
  <pre><code>Msg 156, Level 15, State 1, Line 13
  Incorrect syntax near the keyword 'while'.
  Msg 156, Level 15, State 1, Line 16
  Incorrect syntax near the keyword 'select'.
  Msg 102, Level 15, State 1, Line 16
  Incorrect syntax near ','.
  </code></pre>