How to use CASE within WHERE in SQL Server

Navigation
3

I have the next part of a procedure on sql Server. 

FROM Personas as p 
  inner join Estados as e on p.EstadoId=e.Id
  inner join ListaMarketing_Persona as lmpe on p.Id=lmpe.IdPersona
  inner join ListaMarketing as lm on lmpe.IdListaMarketing=lm.Id 

where lmpe.IdListaMarketing in (select Id from @Temp_LstMark) 
  and 
    case 
      when @p_EstadoCli<>0 then p.EstadoId = @p_EstadoCli end -- si viene diferente de 0 que compare 
      else p.EstadoId<>69  --si vienen 0 que me traiga todos exceto los de estado 69
      end

Currently I get an error in the equal between p.EstadoId = @p_EstadoCli and in p of p.EstadoId<>69 the two errors with the message of Incorrect syntax near x ... I do not know which be the problem.

    
asked by Vitmar Aliaga 28.10.2016 в 17:01
source

1 answer

3

An expression CASE is an expression that returns a result, it can not be used as a stream of logical comparisons. For the same reason, you can rewrite your CASE as: 

where lmpe.IdListaMarketing in (select Id from @Temp_LstMark) 
  and 
    case 
      when p.EstadoId = @p_EstadoCli 
      then 1
      when @p_EstadoCli = 0 AND p.EstadoId <> 69  then 1
      else 0
      end = 1
    
answered by 28.10.2016 / 17:05
source
Do filtering with jquery and php Modify the activity_main_drawer.xml from an external server xml