CODEWITHSUNDEEP
t-sqlif-statementstored-proceduressyntax-error
Mohammad Ali
Mohammad Ali

Reputation: 561

Incorrect syntax near the keyword 'IF', IF after WITH

I am trying to make an IF after a WITH and it is giving me the error Incorrect syntax near the keyword 'IF'

below an example of the stored procedure I am writing

CREATE PROCEDURE [dbo].[Proc_MyProc]
@MODE INT = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

WITH ss as (
    select col1, col2, col3, col4, col5
    from TableTest
)

IF @MODE = 1
    select col1 as A, col2, sum(col5) as col5sum
    from ss
    group by col1, col2
    order by col5sum desc
ELSE IF @MODE = 2
    select col1, col2, sum(col5) as col5sum
    from ss
    group by col1, col2, col3
    order by col5sum desc
ELSE IF @MODE = 3
    select col1, col2, sum(col5) as col5sum
    from ss
    group by col1, col2, col4
    order by col5sum desc

END

GO

I tried to remove the WITH and the syntax error gone, but off course it is not a solution

Thanks :)

Upvotes: 1

Views: 165

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

The WITH goes with the SELECT, so you need to repeat the WITH or use a temporary table.

In this case, though, the WITH really has no value -- let me assume that it is simplified.

The first method would be:

if @MODE = 1
    with ss as (
          select col1, col2, col3, col4, col5
          from TableTest
         )
    select col1 as A, col2, sum(col5) as col3sum
    from #ss
    group by col1, col2
    having SnQuantity > 0
    order by availability desc;
. . .

The second method would be:

select col1, col2, col3, col4, col5
into #ss
from TableTest;

if @MODE = 1
    select col1 as A, col2, sum(col5) as col3sum
    from #ss
    group by col1, col2
    having SnQuantity > 0
    order by availability desc;
. . .

Upvotes: 5

Related Questions