SQL Server:无法访问临时表 [英] SQL Server : cannot access temporary tables
本文介绍了SQL Server:无法访问临时表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何从此查询创建表值函数?
How can I create a table-valued function from this query?
我需要计算开始和结束作业时间之间的时间作为结果 HH:MM
I need to calculate time as result HH:MM
between start and end job time
当我在 SQL 中运行时,此查询有效:
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
无法从函数内部访问临时表.
推荐答案
服务器不允许修改函数中的任何表.改用表变量.
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.
这篇关于SQL Server:无法访问临时表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文