renakre renakre - 6 months ago 37
SQL Question

Using IF BEGIN END with CTE: Incorrect syntax near the keyword `End`

I have checked several questions before posting, but still cannot determine what is wrong with the syntax of the following code? I receive the same error for both

END
. Thanks!

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

A CTE's syntax is as follows

WITH CTE  (Col1, col2, col3,...)  --<-- Column names optional 
AS
 (

   -- CTE's Definition
 )
Select/Delete/Update 
FROM CTE

AS soon as your cte's definition finishes you must Select/Delete/Update from the cte else it is not a valid syntax.

In your query you have done everything alright until defining the CTEs but then not done anything with them ...

Something like .....

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
Comments