SQL Server : cannot access temporary tables

Question

How can I create a table-valued function from this query?

I need to calculate time as result HH:MM between start and end job time

This query work when I run it in SQL :

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 >= @JOBStartDATE
DECLARE @MaxRownum INT
SET @MaxRownum = (SELECT MAX(RowNum) FROM #TEMP)
DECLARE @Iter INT
SET @Iter = (SELECT MIN(RowNum) FROM #TEMP)

WHILE @Iter <= @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

When I try to create I get this error

Cannot access temporary tables from within a function.

Where does #TEMP come from? I'm assuming it's generated outside the function? — lc.
– lc., Commented Jan 27, 2013 at 7:40

alzaimar · Accepted Answer · 2013-01-27 07:42:49Z

18

The server does not allow modification of any table in a function. Use a table variable instead.

declare @temp table (RowNum int, JOBDateTime DateTime, JOBEvent int)

insert into @temp
  SELECT ROW_NUMBER() OVER(ORDER BY JOBID) AS ROWNUM, 
         JOBDateTime,
         JOBEvent  
    FROM JOBsActivityData 
   where JOBID = @USERID and JOBDateTime >= @JOBStartDATE
...

when using table variables, you do not need to drop them.

answered Jan 27, 2013 at 7:42

alzaimar

4,6321 gold badge22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:21:45Z

4

Instead of using a Temp table, use a table variable, that should solve your problem. See this similar question Cannot access temporary tables from within a function

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Jan 27, 2013 at 7:41

Bassam Mehanni

14.9k2 gold badges35 silver badges41 bronze badges

Collectives™ on Stack Overflow

SQL Server : cannot access temporary tables

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related