mardi 19 juin 2018

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 :)

Aucun commentaire:

Enregistrer un commentaire