Case in Where using SQL Server

Question

Case in Where using SQL Server

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have the following code that lists certain business agreements. You have the Yes or No option.

   DECLARE @Opcao VARCHAR(7)

   SET @Opcao = 'S'

   SELECT * FROM 
   BLA BLA BLA

WHERE
    ((('{Opcao}' = 'S') AND NFDataEmis BETWEEN lme.LogMovEstqDataHora AND ppme.ParcPagMovEstqDataVenc) or ('{Opcao}' = 'N'))
    AND ec.CategCodEstr like '{Categoria}' + '%'
    AND crd.ClasseRecDespCodEstr like '{Acordo}' +'%'
    AND me.MovEstqDataMovimento BETWEEN @Data_inicial AND @Data_final

What happens is that it brings me all the options that are listed S and N. I wanted to make a CASE to bring the options that are not in S and N. How could I do that?

SQL SERVER USE

sql-server case

asked by anonymous 21.06.2018 / 22:19

2 answers

1

Include the WHERE

WHERE
        (IsNull(@opcao,'') In ('S') And NFDataEmis Between lme.LogMovEstqDataHora And ppme.ParcPagMovEstqDataVenc)
        Or
        (IsNull(@opcao,'') In ('N') And ec.CategCodEstr Like '{Categoria}' + '%' AND crd.ClasseRecDespCodEstr like '{Acordo}' +'%')

28.06.2018 / 10:27

Alignment of all checkbox to the right Doubt in script logic

score 2 · Accepted Answer

If the variable @Option is empty, it can not execute ((('{Option}' = 'S') AND NFDataEmis .... If it is S or N it executes ( ...)
Option is a variable that will list the records by the Date If it is S it list according to one category if N by another But I want to list them all Dai if for all it does not execute this line:
((('{Opcao}' = 'S') AND NFDataEmis BETWEEN lme.LogMovEstqDataHora AND ppme.ParcPagMovEstqDataVenc) or ('{Opcao}' = 'N'))

The description of the problem seems to me not very clear, apart from the mixture of {Opcao} and @Opcao in the same code. Considering the problem description and subsequent comments from the topic author, here's the code outline.

-- código #1
SELECT ...
  from ...
  where (@Opção is NULL 
         or (@Opção = 'S' 
             and NFDataEmis between lme.LogMovEstqDataHora and ppme.ParcPagMovEstqDataVenc)
         or @Opção = 'N')
        and ...