sql - 如果存在于 SQL Server 中的 CTE 中

Question

我只想知道如何写CTE包含 If Exists在 SQL Server 中？
我曾尝试在下面使用 If Exists 的地方写一个 CTE选择天气记录是否存在的语句。如果记录不存在，那么我将分配默认值，但出现错误

'Incorrect syntax near the keyword 'if'

.'请帮助我修复此错误并指导我编写此 CTE。
请在我写的 CTE 下面找到:-

Alter procedure St_Proc_GetTeamProductionReport      
@mindate DateTime,                        
@maxdate DateTIme,    
@userID varchar(50)                    
as                                          
Begin                                          
     set NoCount on; 
with    
ProductionCTE(CalendarDate,RoleID,UserID,UserECode,UserName,ImmediateSupervisor,NatureOfWorkName,RegionProjectName,CountyName,WorkTypeName,TaskName,VolumneProcessed,TimeSpent,Comment)    
as    
(    
    if exists
    (
        select P.CalendarDate,U.RoleID,U.UserID,U.UserECode,U.UserName,U.ImmediateSupervisor,N.NatureofWorkName,    
        R.RegionProjectName,C.Countyname,W.WorktypeName,T.TaskName,P.VolumeProcessed,P.Timespent,P.Comment    
        from production P inner join NatureOfWork N    
        on N.NatureofWorkID=P.natureofworkid    
        inner join dbo.RegionAndProjectInfo R    
        on R.RegionProjectID=P.RegionProjectID    
        inner join county C    
        on C.countyid=P.countyid    
        inner join worktype W    
        on W.Worktypeid=P.worktypeID    
        inner join task T    
        on T.taskid=P.TaskID    
        inner join UserInfo U    
        on U.Userid=P.userid    
        where P.userid=@userID  and ( convert(varchar, P.CalendarDate, 101) )  between (
        convert(varchar, @mindate, 101) ) and ( convert(varchar, @maxdate, 101) )  
    )
    else
    (
        Select '2012-09-14 13:41:52' as CalendarDate,
        2 as RoleID,'938' as UserID,
        (select Userecode from Userinfo where userid=@userID) as UserECode,
        (select UserName from Userinfo where userid=@userID)as UserName,
        (select ImmediateSupervisor from Userinfo where userid=@userID)as ImmediateSupervisor,
        'BP' as NatureOfWorkName,
        'CO Processing' as RegionProjectName,
        'Adams' as CountyName,
        'Quality' as WorkTypeName,
        'Corrections ' as TaskName,
        5 as VolumneProcessed,
        '01:00' as TimeSpent,
        'test' as Comment
        ) 

    union all

    select P.CalendarDate,U.RoleID,U.UserID,U.UserECode,U.UserName,U.ImmediateSupervisor,N.NatureofWorkName,    
    R.RegionProjectName,C.Countyname,W.WorktypeName,T.TaskName,P.VolumeProcessed,P.Timespent,P.Comment    
    from production P inner join NatureOfWork N    
    on N.NatureofWorkID=P.natureofworkid    
    inner join dbo.RegionAndProjectInfo R    
    on R.RegionProjectID=P.RegionProjectID    
    inner join county C    
    on C.countyid=P.countyid    
    inner join worktype W    
    on W.Worktypeid=P.worktypeID    
    inner join task T    
    on T.taskid=P.TaskID    
    inner join UserInfo U    
    on U.Userid=P.userid    
    inner join ProductionCTE    
    on U.ImmediateSupervisor=ProductionCTE.UserECode    

    where P.IsTaskCompleted=1   and ( convert(varchar, P.CalendarDate, 101) )  between (
    convert(varchar, @mindate, 101) ) and ( convert(varchar, @maxdate, 101) )         
)  
select  distinct CONVERT(VARCHAR,CalendarDate,20) as CalendarDate,UserECode,UserName,NatureOfWorkName,RegionProjectName,CountyName,WorkTypeName,TaskName,VolumneProcessed,TimeSpent,Comment from ProductionCTE where  RoleID=1    
end

去

当我删除这个 If Exist 语句时， CTE工作正常，但在添加 IF Exists 后声明它有错误。

Answer 1

您不能使用 IF EXISTS这样。一个公用表表达式只能包含一个select语句，不能说if condition SELECT THIS else SELECT THAT .

看起来您正在尝试向查询返回的结果集中添加一个额外的创建行。您可以通过将 CTE 实现为将返回单个新行的表值函数来实现这一点。

http://msdn.microsoft.com/en-gb/library/ms191165(v=sql.105).aspx