如何计算使用WITH子句创建的临时表中的记录数

Question

我使用sql server创建了一个存储过程，并且在使用'WITH'子句创建的临时表中不需要计算任何记录。我曾经用那个临时表作为内部联接。除此之外，我想统计该记录临时表中的记录数，并将该值设置为@SlotCount变量。

我这样试过(临时表名称是cte(：

SELECT @SlotCount = COUNT(*) FROM cte

这会产生错误，因为我在存储过程中使用了两次临时表。所以请帮我解决这个问题。谢谢

存储过程：

ALTER PROCEDURE [dbo].[usp_GetXXXXXXXXXXXXXXXXX]
-- Add the parameters for the stored procedure here
@Starttime DATETIME, 
@Endtime DATETIME
AS
BEGIN

DECLARE @SlotCount int

;WITH cte AS ( SELECT @Starttime starttime, DATEADD(MINUTE, 15, @Starttime) endtime
UNION ALL
SELECT endtime, DATEADD(MINUTE, 15, endtime)
FROM cte
WHERE endtime < @Endtime)
--do something for count no of records in cte table and set that value to @SlotCount

select count(a.AVAIL_ID),a.TEACHER_USERNAME 
from cte c
INNER JOIN AvailabilityDetail ad ON c.starttime = ad.StartDateTime AND c. endtime = ad.EndDateTime
INNER JOIN AVAILABILITY a ON ad.AVAIL_ID = a.AVAIL_ID
LEFT JOIN TEACHER_BIO b ON a.TEACHER_USERNAME = b.TEACHER_USERNAME
WHERE ad.IsAvailable = 1
AND ad.AvailabilityType = 'N'
AND b.Active = 1
AND b.IsGroupClass = 1
AND b.HIDE_FROM_SCHEDULING = 0
GROUP BY a.AVAIL_ID,a.TEACHER_USERNAME

END 

Answer 1

您可以将结果存储到临时表中，然后返回

DECLARE @SlotCount int
;WITH cte AS ( SELECT @Starttime starttime, DATEADD(MINUTE, 15, @Starttime) endtime
UNION ALL
SELECT endtime, DATEADD(MINUTE, 15, endtime)
FROM cte
WHERE endtime < @Endtime)

select count(a.AVAIL_ID),a.TEACHER_USERNAME 
INTO #cte from cte c
INNER JOIN AvailabilityDetail ad ON c.starttime = ad.StartDateTime AND c. endtime = ad.EndDateTime
INNER JOIN AVAILABILITY a ON ad.AVAIL_ID = a.AVAIL_ID
LEFT JOIN TEACHER_BIO b ON a.TEACHER_USERNAME = b.TEACHER_USERNAME
WHERE ad.IsAvailable = 1
AND ad.AvailabilityType = 'N'
AND b.Active = 1
AND b.IsGroupClass = 1
AND b.HIDE_FROM_SCHEDULING = 0
GROUP BY a.AVAIL_ID,a.TEACHER_USERNAME
SELECT * FROM #cte
SET @SlotCount = (SELECT COUNT(*) FROM #cte)