If Exists inside a CTE in SQl Server

Question

I just want to know that How to write a CTE containing If Exists in SQl Server ? I had tried to write a CTE below where i am Using If Exists Statement to select weather the record exist or not .In case if the record does not exist then i am assigning default value but i am getting error

'Incorrect syntax near the keyword 'if'

.' Please help me to fix this error and guide me to write this CTE. Please find below the CTE which i had written:-

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

GO

When i am removing this If Exist statement then the CTE is working fine but after adding the IF Exists statement it is having error.

Answer 1

You can't use IF EXISTS in CTE. But you can modify logic of function

Example:

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    
(               
        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, 1 AS cnt 
        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) )  
        UNION ALL
        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, 0 
     ), ProductionCTE2 AS    
    (    
    SELECT TOP(SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE COUNT(*) END FROM ProductionCTE WHERE cnt = 1)
           CalendarDate,RoleID,UserID,UserECode,UserName,ImmediateSupervisor,NatureofWorkName,    
           RegionProjectName,Countyname,WorktypeName,TaskName,VolumeProcessed,Timespent,Comment
    FROM ProductionCTE2           
    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 ProductionCTE2
where  RoleID=1    
end

Answer 2

If you want to construct a UNION where you only get a result from the second SELECT if the first SELECT returns no rows, you can achieve this using RANK(). So, you can place your real query in the first SELECT and the desired default values in the second, and achieve the results you want.

I don't have your tables and data, so I'm not going to attempt to re-write your query. But I'll illustrate with this:

;With WithDefaults as (
    select name,0 as Rank from sys.objects
    union all
    select 'abc',1 --Default if no results
), Ranked as (
    select *,RANK() OVER (ORDER BY Rank) as Rnk from WithDefaults
)
select * from Ranked where Rnk = 1

Returns (on a mostly empty DB I tried it on) 98 results, of which none had the name abc. If we force the first SELECT to return no results:

    select name,0 as Rank from sys.objects where 1 = 2

We now get a single row result with the name abc.

Hopefully, you can see how this could apply to your original query.

Answer 3

You can't use IF EXISTS in this way. A common table expression must only contain a single select statement, it's not possible to say if condition SELECT THIS else SELECT THAT.

It looks like you are trying to add an additional created row to a resultset returned by a query. You could achieve this by implementing the CTE as a table-valued function that would return the single new row.

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