error 156 severity 15 state 1 incorrect syntax near the keyword IF

Question

Error at Line Number 25: I am writing stored procedure which should return data based on the combination selected by the user. For Example I have 3 different type of filter(City/Area/VendorType). If User selects City=xyz and Area=pqr then result should be all data with City=xyz and Area=pqr only. If None of the option is selected by the user then I am setting value for all 3 parameter is equals to null AND should return all row from the database. Note : Above 3 column should always have data in database. I have created one [WEBMethod] inside which I am calling this stored procedure using sql connection. And passing all parameters using AJAX jquery.

 ALTER PROCEDURE [dbo].spGetVendorbyFilter
                      @PageNumber INT,
                      @PageSize   INT,
                      @city       VARCHAR(200),
                      @area       VARCHAR(200),
                      @vendortype VARCHAR(200)
AS
  BEGIN
      DECLARE @StartRow INT
      DECLARE @EndRow INT

      SET @StartRow = ( ( @PageNumber - 1 ) * @PageSize ) + 1;
      SET @EndRow= @PageNumber * @PageSize;

      WITH Result
           AS (
               SELECT *,
                      Row_number()
                        OVER (
                          ORDER BY VendorID ASC) RowNumber
               FROM   tblVendor

              ) 

     IF (@city IS NOT NULL AND @area IS NULL AND @vendortype IS NULL)
      SELECT *
      FROM  Result where City=@city AND RowNumber BETWEEN @StartRow and @EndRow
     ELSE IF (@city IS NULL AND @area IS NOT NULL AND @vendortype IS NULL)
       SELECT *
      FROM  Result where Area=@area AND RowNumber BETWEEN @StartRow and @EndRow
     ELSE IF (@city IS NULL AND @area IS NULL AND @vendortype IS NOT NULL)
       SELECT *
      FROM  Result where Category=@vendortype AND RowNumber BETWEEN @StartRow and @EndRow
     ELSE IF (@city IS NOT NULL AND @area IS  NOT NULL AND @vendortype IS NULL)
       SELECT *
      FROM  Result where City=@city And Area=@area AND RowNumber BETWEEN @StartRow and @EndRow
     ELSE IF (@city IS NOT NULL AND @area IS NULL AND @vendortype IS NOT NULL)
       SELECT *
      FROM  Result where City=@city And Category=@vendortype AND RowNumber BETWEEN @StartRow and @EndRow
     ELSE IF (@city IS NULL AND @area IS  NOT NULL AND @vendortype IS NOT NULL)
       SELECT *
      FROM  Result where Area=@area And Category=@vendortype AND RowNumber BETWEEN @StartRow and @EndRow
     ELSE 
         SELECT *
      FROM  Result WHERE RowNumber BETWEEN @StartRow and @EndRow

END

Sorry Milan, you cannot use an IF statement to generate a conditional CTE. Am I right in thinking, you want the parameters Area and VendorType to be optional? — David Rushton, Dec 16 '16 at 10:04
@Destination - Thanks for your Response. May be you are right. If user select 3 options(City/Area/Vendortype) then it should have data accordingly.and If none of the option is selected then it should display all the data. — MRG, Dec 16 '16 at 10:37

Pரதீப் · Answer 1 · 2016-12-16T10:16:25.123

2

You cannot use a CTE like that. CTE should end with either of these SELECT/INSERT/DELETE.

According to your If condition when @city is NULL your ELSE condition will fail since you are comparing City=@city. If am not wrong you are looking for this.

ALTER PROCEDURE [dbo].[Spgetvendorbyfilter] @PageNumber INT,
                                            @PageSize   INT,
                                            @city       VARCHAR(200),
                                            @area       VARCHAR(200),
                                            @vendortype VARCHAR(200)
AS
  BEGIN
      DECLARE @StartRow INT
      DECLARE @EndRow INT

      SET @StartRow = ( ( @PageNumber - 1 ) * @PageSize ) + 1;
      SET @EndRow= @PageNumber * @PageSize;

      WITH Result
           AS (SELECT *,
                      Row_number()
                        OVER (
                          ORDER BY VendorID ASC) RowNumber
               FROM   tblVendor
               WHERE  ( City = @city
                         OR @city IS NULL )
                      AND ( Area = @area
                             OR @area IS NULL )
                      AND ( Category = @vendortype
                             OR @vendortype IS NULL ))
      SELECT *
      FROM   Result
      WHERE  RowNumber BETWEEN @StartRow AND @EndRow
  END

edited Dec 16 '16 at 10:16

answered Dec 16 '16 at 09:58

Pரதீப்

91,748
19
131
172

Thanks for your quick reply. Actually I am new in Stored procedure. Could you please help with correct syntax. – MRG Dec 16 '16 at 10:02
@MilanGadhiya - Did you try my answer – Pரதீப் Dec 16 '16 at 10:06
I am writing above stored procedure to extract data with different filted combination. – MRG Dec 16 '16 at 10:14
Yes I have tried your code. it is giving syntax error while passing parameter.(Procedure execution failed 42000 - [SQL Server]Incorrect syntax near '@PageNumber'. ) Actually I am writing above stored procedure to extract data with different filtered combination. For Example When user selects City(xyz) and area(pqr). Parameter value should be City=pqr and area=xyz and Vendortype=Null and should return all data with City=pqr and area=xyz. AND if none of the option is selected then it should have all data. – MRG Dec 16 '16 at 10:24
@MilanGadhiya - check the latest code.. – Pரதீப் Dec 16 '16 at 10:26
Still it is giving same error(Procedure execution failed 42000 - [SQL Server]Incorrect syntax near '@PageNumber') while passing the parameters. Thanks – MRG Dec 16 '16 at 10:34
How are you calling that procedure ? – Pரதீப் Dec 16 '16 at 10:43
I have created one web service inside which i am calling procedure using sql connection and passing parameters through jquery – MRG Dec 16 '16 at 10:46
@MilanGadhiya - can you show the exec of procedure call ? – Pரதீப் Dec 16 '16 at 10:49
Can you please tell me exactly which lines of code you want from my side. – MRG Dec 16 '16 at 11:01

error 156 severity 15 state 1 incorrect syntax near the keyword IF

1 Answers1