title: SQL Server : cannot access temporary tables
body: <p>How can I create a table-valued function from this query?</p>
  
  <p>I need to calculate time as result <code>HH:MM</code> between start and end job time  </p>
  
  <p>This query work when I run it in SQL :</p>
  
  <pre><code>DECLARE @USERID int;
  SET @USERID = 10
  
  DECLARE @JOBStartDATE DATETIME;
  SET @JOBStartDATE = (SELECT StartJOBHoursDATE FROM JOBs WHERE ID=@USERID) 
  
  
  DECLARE @StartTime DATETIME;
  DECLARE @JOBDateTime DATETIME;
  DECLARE @JOBEvent nvarchar(50);
  DECLARE @totalTime int;
  SET @totalTime = 0;
  
  SELECT  ROW_NUMBER() OVER(ORDER BY JOBID) AS ROWNUM, JOBDateTime,JOBEvent  INTO #TEMP FROM  JOBsActivityData where JOBID = @USERID and JOBDateTime &gt;= @JOBStartDATE
  DECLARE @MaxRownum INT
  SET @MaxRownum = (SELECT MAX(RowNum) FROM #TEMP)
  DECLARE @Iter INT
  SET @Iter = (SELECT MIN(RowNum) FROM #TEMP)
  
  WHILE @Iter &lt;= @MaxRownum
  BEGIN
  SET @JOBDateTime =(SELECT JOBDateTime FROM #TEMP WHERE RowNum = @Iter)
  SET @JOBEvent =(SELECT JOBEvent FROM #TEMP WHERE RowNum = @Iter)
  IF(@JOBEvent = 'START')
  BEGIN
  SET @StartTime =(SELECT JOBDateTime FROM #TEMP WHERE RowNum = @Iter)
  END
  IF(@JOBEvent = 'END' AND @StartTime IS NOT NULL)
  BEGIN
  SET @totalTime = @totalTime + (SELECT DATEDIFF(minute,@StartTime,@JOBDateTime))
  SET @StartTime = NULL;
  END
  
      SET @Iter = @Iter + 1
  END
  
  DROP TABLE #TEMP
  
  SELECT CAST((@totalTime / 60) AS VARCHAR(8)) + ':' + 
         CAST((@totalTime % 60) AS VARCHAR(2)) AS JOBHours
  </code></pre>
  
  <p>When I try to create I get this error </p>
  
  <blockquote>
    <p><em>Cannot access temporary tables from within a function.</em></p>
  </blockquote>