CODEWITHSUNDEEP
sqlsql-server
Mohammad hossein
Mohammad hossein

Reputation: 255

SQL Error: Incorrect syntax error

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'.

Upvotes: 0

Views: 482

Answers (1)

Martin Smith
Martin Smith

Reputation: 452967

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.

Upvotes: 6

Related Questions