title: Is it always possible to transform multiple spatial selects with while loop and variables into a single query without using temp tables in sql?
body: <p>This problem can be solved with temp table, however, <strong>I don't want to use Temp table or var table</strong>, this question is mostly for my personal educational purposes.</p>
  
  <p>I inherited the following SQL:</p>
  
  <pre><code>DECLARE  @i int = 993
  
  while @i &lt;=1000
  begin
  declare @lat nvarchar(20)
  select top 1 @lat = SUBSTRING(Address,0,CHARINDEX(',',Address,0)) from dbo.rent
      where id  = @i; 
  declare @lon nvarchar(20)
  select top 1 @lon = SUBSTRING(Address,CHARINDEX(',',Address)+1,LEN(Address)) from dbo.rent
      where id  = @i 
  
  
  declare @p GEOGRAPHY =  GEOGRAPHY::STGeomFromText('POINT('+ @lat +' '+@lon+')', 4326)
  
  
  select price/LivingArea sq_m, (price/LivingArea)/avg_sq_m, * from
  (select     (sum(price)/sum(LivingArea)) avg_sq_m,  count(1) cnt, @i id from 
          (select  *, GEOGRAPHY::STGeomFromText('POINT('+ 
          convert(nvarchar(20), SUBSTRING(Address,0,CHARINDEX(',',Address,0)))+' '+
          convert( nvarchar(20), SUBSTRING(Address,CHARINDEX(',',Address)+1,LEN(Address)))+')', 4326)
              .STBuffer(500).STIntersects(@p) as [Intersects]
              from dbo.rent
              where Address is not null 
          ) s
      where [Intersects] = 1) prox
      inner join dbo.rent r on  prox.id = r.id
      set @i = @i+1
  end
  </code></pre>
  
  <p>it is used to analyze property prices per square meter that are in proximity and compare them to see which ones are cheaper... </p>
  
  <p>Problem: a mechanism for calling has to be moved from <code>C#</code> to <code>SQL</code> and all queries have to be combined into a single result (now you get one row per one while run), i.e <code>@i</code> and <code>@p</code> has to go and become <code>while id &lt; x and id &gt; y</code> or somehow magically joined, </p>
  
  <p>the procedure is a cut down version of actual thing but having a solution to the above I will have no problem making the whole thing work...</p>
  
  <p>I am of the opinion that any SQL mechanism with variables and loops can be transformed to a single SQL statement, hence the question.</p>
  
  <p><a href="http://sqlfiddle.com/#!18/f72284/27" rel="nofollow noreferrer">SqlFiddle</a></p>