将IF BEGIN END与CTE一起使用:关键字“END”附近的语法不正确

Question

在发布之前，我已经检查了几个问题，但仍然无法确定以下代码的语法有什么问题？我收到两个END的相同错误。谢谢

IF @UserId =''
BEGIN
  ;WITH cte AS (
   SELECT * FROM (
        SELECT  [EntryId],              
                CAST(ROW_NUMBER() OVER (ORDER BY Date DESC) AS INT) AS RN_PARENT,
                0 AS RN_CHILD
        FROM    Entries
        WHERE   [EntryDepthness] = 0 AND DiscussionWallId = @DiscussionWallId 
    ) AS Main
        WHERE ((RN_PARENT BETWEEN(@PageIndex -1) * (@PageSize) + 1   AND (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1))
        UNION ALL
        SELECT  e.[EntryId],            
                0 AS RN_PARENT,
                CAST(ROW_NUMBER() OVER (ORDER BY e.Date DESC) AS INT) AS RN_CHILD      
        FROM    Entries e
                INNER JOIN cte v ON v.EntryId = e.ParentEntryId 
                WHERE e.EntryDepthness = 1
    )
END
ELSE
BEGIN
  ;WITH cte AS (
   SELECT * FROM (
        SELECT  [EntryId],              
                CAST(ROW_NUMBER() OVER (ORDER BY Date DESC) AS INT) AS RN_PARENT,
                0 AS RN_CHILD
        FROM    Entries
        WHERE   [EntryDepthness] = 0 AND DiscussionWallId = @DiscussionWallId AND 
                UserId IN (
                        SELECT UserId FROM GroupStudentAssignments 
                        WHERE MemberId=@UserId AND GroupId IN (SELECT GroupId FROM GroupDiscussionRegistrations WHERE DiscussionWallId=@DiscussionWallId)
                )
    ) AS Main
    WHERE ((RN_PARENT BETWEEN(@PageIndex -1) * (@PageSize) + 1   AND (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1))
    UNION ALL
    SELECT  e.[EntryId],            
            0 AS RN_PARENT,
            CAST(ROW_NUMBER() OVER (ORDER BY e.Date DESC) AS INT) AS RN_CHILD      
    FROM    Entries e
            INNER JOIN cte v ON v.EntryId = e.ParentEntryId 
            WHERE e.EntryDepthness = 1
)
END

Answer 1

CTE的语法如下

WITH CTE  (Col1, col2, col3,...)  --<-- Column names optional 
AS
 (
   -- CTE's Definition
 )
Select/Delete/Update 
FROM CTE

cte的定义完成后，必须立即从cte中选择/删除/更新，否则它不是有效的语法。

在您的查询中，在定义CTE之前，您已经做了所有事情，但没有对它们做任何事情。。。

类似。。。。。

IF @UserId =''
BEGIN
  ;WITH cte AS (
   SELECT * FROM (
        SELECT  [EntryId],              
                CAST(ROW_NUMBER() OVER (ORDER BY Date DESC) AS INT) AS RN_PARENT,
                0 AS RN_CHILD
        FROM    Entries
        WHERE   [EntryDepthness] = 0 AND DiscussionWallId = @DiscussionWallId 
    ) AS Main
        WHERE ((RN_PARENT BETWEEN(@PageIndex -1) * (@PageSize) + 1  
             AND (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1))
        UNION ALL
        SELECT  e.[EntryId],            
                0 AS RN_PARENT,
                CAST(ROW_NUMBER() OVER (ORDER BY e.Date DESC) AS INT) AS RN_CHILD      
        FROM    Entries e
                INNER JOIN cte v ON v.EntryId = e.ParentEntryId 
                WHERE e.EntryDepthness = 1
    )
    select * from cte   --<-- maybe a select statment here 
END
ELSE
BEGIN
  ;WITH cte AS (
   SELECT * FROM (
        SELECT  [EntryId],              
                CAST(ROW_NUMBER() OVER (ORDER BY Date DESC) AS INT) AS RN_PARENT,
                0 AS RN_CHILD
        FROM    Entries
        WHERE   [EntryDepthness] = 0 AND DiscussionWallId = @DiscussionWallId AND 
                UserId IN (
                        SELECT UserId FROM GroupStudentAssignments 
                        WHERE MemberId=@UserId 
                        AND GroupId IN (SELECT GroupId 
                                        FROM GroupDiscussionRegistrations 
                                        WHERE DiscussionWallId=@DiscussionWallId)
                )
    ) AS Main
    WHERE ((RN_PARENT BETWEEN(@PageIndex -1) * (@PageSize) + 1   
             AND (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1))
    UNION ALL
    SELECT  e.[EntryId],            
            0 AS RN_PARENT,
            CAST(ROW_NUMBER() OVER (ORDER BY e.Date DESC) AS INT) AS RN_CHILD      
    FROM    Entries e
            INNER JOIN cte v ON v.EntryId = e.ParentEntryId 
            WHERE e.EntryDepthness = 1
)
select * from cte    --<-- and maybe a select statment here 
END

Answer 2

您不需要IF/ELSE块来执行此操作。

您所需要的只是另一个OR条件来检查@UserId =''。试试这个方法。

;WITH cte 
     AS (SELECT * 
         FROM   (SELECT [entryid], 
                        Row_number() OVER (ORDER BY date DESC) AS RN_PARENT, 
                        0 AS RN_CHILD 
                 FROM   entries 
                 WHERE  [entrydepthness] = 0 
                        AND discussionwallid = @DiscussionWallId 
                        AND ( userid IN (SELECT userid 
                                         FROM   groupstudentassignments 
                                         WHERE  memberid = @UserId 
                                                AND groupid IN (SELECT groupid FROM groupdiscussionregistrations 
                                                                WHERE discussionwallid = @DiscussionWallId) 
                                            ) OR @UserId = '' )) AS Main --Here
         WHERE (( rn_parent BETWEEN( @PageIndex - 1 ) * ( @PageSize ) + 1 
         AND ( ( ( @PageIndex - 1 ) * @PageSize + 1 ) + @PageSize ) - 1 )) 
         UNION ALL 
         SELECT e.[entryid], 
                0                                      AS RN_PARENT, 
                Row_number() OVER (ORDER BY e.date DESC)  AS RN_CHILD 
         FROM   entries e 
                INNER JOIN cte v 
                        ON v.entryid = e.parententryid 
         WHERE  e.entrydepthness = 1) 
SELECT * 
FROM   cte 

还为根本不需要的ROW_NUMBER移除Cast。ROW_NUMBER不生成decimal值。

您当前的IF/ELSE块可能会导致参数嗅探