SQL Server : cannot access temporary tables
sql, sql-server, t-sql
Solution
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.
Problem
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.