SQL Server : cannot access temporary tables
12,261
Solution 1
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.
Solution 2
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
Author by
akaco
Updated on June 16, 2022Comments
-
akaco about 2 years
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 timeThis 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.