存储过程中
with tableName
{
select * from table1
}select * from table1 ------第一次
.
.
.
.
select * from table1 ------第二次
为什么第二次就不能使用table1了那 ?
我如何才能使用多次那 ?谢谢
with tableName
{
select * from table1
}select * from table1 ------第一次
.
.
.
.
select * from table1 ------第二次
为什么第二次就不能使用table1了那 ?
我如何才能使用多次那 ?谢谢
BEGIN SET NOCOUNT ON
DECLARE @CurrentSkillsetID int
DECLARE @CurrentCourseID int
DECLARE @IsCourseExpired bit
DECLARE @ExpiredCourseRowNum int
IF isnull(@CurrentSkillsetID,0) = 0
BEGIN
-----select all the course in current series
WITH tempCurrentSeriesCourse AS
(
SELECT ROW_NUMBER() OVER(ORDER BY st.SuiteID ASC, ss.Sequence ASC ,c.Sequence ASC) CourseOrder ,
c.CourseID,
c.SkillsetID,
cu.CourseUserStatusID,
cu.StartTime,
c.ExpirationDays
FROM CourseUser cu LEFT OUTER JOIN Course c ON cu.CourseID = c.CourseID
LEFT OUTER JOIN Skillset ss ON c.SkillsetID = ss.SkillsetID
LEFT OUTER JOIN Suite st ON ss.SuiteID = st.SuiteID
WHERE cu.UserID = @UserID AND st.SeriesID = @SeriesID
)
----get the first pending course 's skillset
SELECT @CurrentSkillsetID = SkillsetID
FROM tempCurrentSeriesCourse
WHERE CourseUserStatusID = 0
----expired course condition
SELECT @CurrentCourseID = CourseID
FROM tempCurrentSeriesCourse
WHERE CourseUserStatusID = 2 IF ISNULL(@CurrentSkillsetID,0) <> 0
BEGIN SELECT @IsCourseExpired = CASE WHEN DATEADD("day", tcsc.ExpirationDays, tcsc.StartTime) <= Getdate() then 1
ELSE 0
END
FROM tempCurrentSeriesCourse tcsc
WHERE tcsc.CourseUserStatusID = 2
IF @IsCourseExpired = 1
BEGIN
SELECT @ExpiredCourseRowNum = CourseOrder from tempCurrentSeriesCourse
WHERE CourseUserStatusID = 2 SELECT @CurrentSkillsetID = SkillsetID
FROM tempCurrentSeriesCourse
WHERE CourseOrder = @ExpiredCourseRowNum
END
ELSE
BEGIN
SELECT @CurrentSkillsetID = SkillsetID
FROM Course
WHERE CourseID = @CurrentCourseID
END
END
END
SELECT c.[CourseID]
,c.[SkillsetID]
,c.[Sequence]
,c.[CourseNumber]
,c.[CourseTitle]
,c.[CourseDescription]
,c.[CourseTypeID]
,c.[CourseStatusID]
,c.[AuthorID]
,c.[OriginalCourseID]
,c.[RoundsRequired]
,c.[ExpirationDays]
,c.[GUID]
,cu.CourseUserStatusID
,cu.Competency
,CourseUserStatusText = CASE CourseUserStatusID WHEN 0 THEN 'Pending'
WHEN 1 THEN 'In Progress'
WHEN 2 THEN 'Expired'
WHEN 3 THEN 'Completed'
END
FROM [Course] c LEFT OUTER JOIN CourseUser cu ON c.CourseID = cu.CourseID
WHERE SkillsetID = @CurrentSkillsetID AND cu.UserID = @UserID SET NOCOUNT OFF
END
with 语句只能在它最近的语句使用
create PROCEDURE p1
as
BEGIN
with temp1 as
(
select * from t1
)
select * from t2
select * from temp1
end消息 208,级别 16,状态 1,过程 p1,第 9 行
对象名 'temp1' 无效。
相关解释:http://www.sql-server-performance.com/art_cte_sql2005.asp