Case When in SQL procedure

Question

I'm trying to use a case when in a sql procedure.

What i want is that when the variable @ID_ETAPA is 6 the where is a little diferent. But i'm getting multiple erros. Incorrect syntax near = And Incorrect syntax near ELSE Someone knows what i'm missing?

WHERE CASE 
      WHEN @ID_ETAPA=6 THEN 
        SS.ID_STATUSSOLICITACAO = 1 -- PENDENTES
            AND SS.Id_etapa = @ID_ETAPA 
        Else
            SS.ID_STATUSSOLICITACAO = 1 -- PENDENTES
   END

THE full select with error in the where clause.

SELECT DISTINCT CF.NOM_FILIAL,
        SS.COD_SOLICITACAO,
        SS.COD_GTV,
        SS.SEQ_GTV,
        SS.COD_SERIEGTV,
        ST.DES_TPSERVICO,
        SSI.DES_SISTEMAANALISE,
        SSI.ID_SISTEMAANALISE,
        SM.DES_MOTIVOSOLICITACAO,
        SE.DES_ETAPA,
        SS.ID_ETAPA,
        SST.DES_STATUSSOLICITACAO,
        CFSOLICITANTE.NOM_FUNC      AS NOM_SOLICITANTE,
        CFSUCESSOR.NOM_FUNC     AS NOM_SUCESSOR,
        CFOPERADOR.NOM_FUNC     AS NOM_OPERADOR,
        SS.DES_DETALHAMENTOGTV,
        SA.ID_GRUPOACESSO

INTO #DADOS

FROM SSE_SOLICITACAO SS

INNER JOIN COR_FILIAL CF
ON  CF.COD_REGIONAL     = SS.COD_REGIONAL
    AND CF.COD_FILIAL   = SS.COD_FILIAL

INNER JOIN SSE_TPSERVICO ST
ON  ST.ID_TPSERVICO = SS.ID_TPSERVICO

INNER JOIN SSE_SISTEMAANALISE SSI
ON  SSI.ID_SISTEMAANALISE = SS.ID_SISTEMAANALISE

INNER JOIN SSE_MOTIVOSOLICITACAO SM
ON  SM.ID_MOTIVOSOLICITACAO = SS.ID_MOTIVOSOLICITACAO

INNER JOIN SSE_ETAPA SE
ON  SE.ID_ETAPA = SS.ID_ETAPA

INNER JOIN SSE_STATUSSOLICITACAO SST
ON  SST.ID_STATUSSOLICITACAO = SS.ID_STATUSSOLICITACAO

INNER JOIN COR_FUNCIONARIO CFSOLICITANTE
ON  CFSOLICITANTE.COD_FUNC = SS.COD_FUNCSOLICITANTE

INNER JOIN SSE_ACESSO SA
ON  SA.COD_FUNCSOLICITANTE = SS.COD_FUNCSOLICITANTE

LEFT JOIN COR_FUNCIONARIO CFSUCESSOR
ON  CFSUCESSOR.COD_FUNC = SS.COD_FUNCSUCESSOR

LEFT JOIN COR_FUNCIONARIO CFOPERADOR
ON  CFOPERADOR.COD_FUNC = SS.COD_FUNCOPERADOR

WHERE SS.ID_STATUSSOLICITACAO = CASE 
      WHEN @ID_ETAPA=6 THEN 
        SS.ID_STATUSSOLICITACAO = 1 -- PENDENTES
            AND SS.Id_etapa = @ID_ETAPA 
        Else
            SS.ID_STATUSSOLICITACAO = 1 -- PENDENTES
   END

ORDER BY SS.COD_SOLICITACAO

Might be purple's answer will work becausewher condition need operand in condition.... — Dipak Delvadiya, Apr 07 '17 at 19:23

score 0 · Answer 1 · edited May 23 '17 at 12:10

0

The CASE statement must be part of the WHERE statement. Try:

WHERE SS.ID_STATUSSOLICITACAO = CASE 
      WHEN @ID_ETAPA=6 THEN 
        SS.ID_STATUSSOLICITACAO = 1 -- PENDENTES
            AND SS.Id_etapa = @ID_ETAPA 
        Else
            SS.ID_STATUSSOLICITACAO = 1 -- PENDENTES
   END

See this post for more information: "CASE" statement within "WHERE" clause in SQL Server 2008

EDIT

Try taking SS.ID_STATUSSOLICITACAO = 1 out of the CASE expression and placing it afterwards:

WHERE SS.Id_etapa = 
    CASE WHEN @ID_ETAPA=6 THEN @ID_ETAPA 
        ELSE 0 --- What you use for this value depends on what other values are possible for the @ID_ETAPA variable of course
    END
AND SS.ID_STATUSSOLICITACAO = 1

edited May 23 '17 at 12:10

Community

1
1

answered Apr 07 '17 at 19:20

Purple Lady

481
5
15

I'm still getting the errors in the "=" and in the "Else".Both syntax erros. – Vinicius Cano Apr 07 '17 at 19:23
Can you provide the SELECT statement used right before the WHERE clause? – Purple Lady Apr 07 '17 at 19:25
I updated the answer, but it's important to consider how many different values are possible for your @ID_ETAPA variable. I used "ELSE 0" as a placeholder, but you will want to change it to fit your needs. – Purple Lady Apr 07 '17 at 20:32

Case When in SQL procedure

1 Answers1