SQL Error: Incorrect syntax error

Question

What is wrong in this procedure

CREATE PROCEDURE [dbo].[Question_ReadBySort] 
-- Add the parameters for the stored procedure here
(
    @PageNumber int,
    @Gid bigint,
    @Sorttype int,
    @Df int
)
AS
BEGIN
    if @Gid=0
      BEGIN
       With Cust AS 
          (SELECT * ,
           ROW_NUMBER() OVER (order by q_id DESC) as RowNumber 
           from tbl_Question
           where q_del=0)
      END
    ELSE
        BEGIN
        With Cust AS 
           (SELECT * ,
           ROW_NUMBER() OVER (order by q_id DESC) as RowNumber 
           from tbl_Question
           where q_del=1)
        END
END
GO

and this error occur in SQL Server:

Msg 156, Level 15, State 1, Procedure Question_ReadBySort, Line 23
Incorrect syntax near the keyword 'END'.

Msg 156, Level 15, State 1, Procedure Question_ReadBySort, Line 31
Incorrect syntax near the keyword 'END'.

Have you sequentially reduced each part of the query to eliminate the culprits and zero in on the actual problem, or just here asking us to figure it out from scratch? — Grant Thomas
– Grant Thomas, Commented May 30, 2013 at 10:17

Martin Smith · Accepted Answer · 2013-05-30 10:17:27Z

6

A CTE definition must be immediately followed by a statement using the CTE. For example something like

WITH Cust
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY q_id DESC) AS RowNumber
         FROM   tbl_Question
         WHERE  q_del = CASE
                          WHEN @Gid = 0 THEN 0
                          ELSE 1
                        END)
SELECT *
FROM   Cust

You can't define different CTE definitions conditionally with IF then use them later on as you appear to be trying.

answered May 30, 2013 at 10:17

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Error: Incorrect syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related