Condition in the trial

Question

Good afternoon.

I have a filter, I have several filters. one of these filters is @ST_TIPO

-When I get by Parametro BSP I want to filter just by PT.ST_TIPO = 'BSP'

-When I get by Parametro 'DIS' I want to filter just by PT.ST_TIPO = 'DIS'

-When I Get BSPDIS I want to filter through pt.ST_TIPO = 'BSP' or 'DIS'

-When I get OUT want to filter PT.ST_tipo <> 'BSP' and PT.ST_tipo <> 'DIS'

I can’t make an IF @ST_TIPO = 'BSP_DIS'

follows below my code.

Alter PROCEDURE BCT_SP_PESSOA_BUSCAR_POR_GERENTE
     @ID_GERENTE VARCHAR(50)
    ,@ST_TIPO CHAR(3) = NULL
    ,@NR_CNPJ NUMERIC = NULL
    ,@DS_NOME VARCHAR(100) = NULL
    ,@ID_CODJDE VARCHAR(8) = NULL

AS

    SELECT DISTINCT
         PE.ID_PESSOA
        ,PE.DS_NOME
        ,PE.DS_RAZAOSOCIAL
        ,PE.NR_CPFCNPJ
        ,PT.ST_TIPO
        ,PE.ID_CODJDE
        ,AB.ID_SALESORGANIZATION
        ,PE.ST_CLASSE
        ,PE.ID_CUSTOMERNUMBER
    FROM
        BCT_PESSOA  PE
        INNER JOIN BCT_PESSOATIPO PT ON PT.ID_PESSOA = PE.ID_PESSOA
        INNER JOIN BCT_GRUPOPESSOA GP ON PE.ID_PESSOA = GP.ID_PESSOA
        INNER JOIN BGC_GRUPO_GERENTE GG ON GG.ID_GRUPO = GP.ID_GRUPO
            outer apply (
        select top 1 *
        from BSP_ADDRESSBOOK AB 
        where AB.ID_CUSTOMERNUMBER = PE.ID_CUSTOMERNUMBER
        and AB.ID_SALESORGANIZATION = PE.ID_SALESORGANIZATION
        )
        AB
    WHERE
        GG.ID_GERENTE = @ID_GERENTE

        AND (PT.ST_TIPO = ISNULL(@ST_TIPO,PT.ST_TIPO) or (@ST_TIPO = 'OUT' and PT.ST_tipo <> 'BSP' and PT.ST_tipo <> 'DIS'))
        AND (PE.NR_CPFCNPJ = null OR null IS NULL)
        AND (PE.DS_RAZAOSOCIAL LIKE '%' + null + '%' OR @DS_NOME IS NULL)
        AND (PE.ID_CODJDE = @ID_CODJDE OR @ID_CODJDE IS NULL)
        AND AB.ID_PARTNERFUNCTION = 'SP'
    ORDER BY
         PE.DS_RAZAOSOCIAL
        ,PE.NR_CPFCNPJ

Answer 1

Try

-- código #1 v2
SELECT ...
  from ...
  where GG.ID_GERENTE = @ID_GERENTE
        and 1 = case when @ST_TIPO is null
                         then 1
                     when @ST_TIPO = 'BSP'
                        then case when PT.ST_TIPO = 'BSP' then 1 else 0 end
                     when @ST_TIPO = 'DIS'
                        then case when PT.ST_TIPO = 'DIS' then 1 else 0 end
                     when @ST_TIPO = 'BSPDIS'
                        then case when (PT.ST_TIPO = 'BSP' or PT.ST_TIPO = 'DIS') then 1 else 0 end
                     when @ST_TIPO = 'OUT'
                        then case when (PT.ST_TIPO <> 'BSP' and PT.ST_TIPO <> 'DIS') then 1 else 0 end
                     else 0                  
                end
        and ...

---

Developing the approach you used, here’s another option:

-- código #3
SELECT ...
  from ...
  where GG.ID_GERENTE = @ID_GERENTE
        and (@ST_TIPO is null 
             or (@ST_TIPO = 'BSP' and (PT.ST_TIPO = 'BSP'))
             or (@ST_TIPO = 'DIS' and (PT.ST_TIPO = 'DIS'))
             or (@ST_TIPO = 'BSPDIS' and (PT.ST_TIPO = 'BSP' or PT.ST_TIPO = 'DIS'))
             or (@ST_TIPO = 'OUT' and (PT.ST_tipo <> 'BSP' and PT.ST_tipo <> 'DIS'))    
            )
        and ...

---

To test the two approaches:

-- código #2
use tempDB;
go

CREATE TABLE BCT_PESSOATIPO (ID_PESSOA int, ST_TIPO char(3));
go

INSERT into BCT_PESSOATIPO values
  (1, 'DIS'), (2, 'BSP'), (3, 'XXX'), 
  (4, 'DIS'), (5, 'DIX'), (6, 'ABC');
go

declare @ST_TIPO varchar(6);
set @ST_TIPO= 'BSPDIS';  

--
SELECT ID_PESSOA, ST_TIPO  
  from BCT_PESSOATIPO;

--
SELECT ID_PESSOA, ST_TIPO
  from BCT_PESSOATIPO as PT
  where (@ST_TIPO is null 
         or (@ST_TIPO = 'BSP' and (PT.ST_TIPO = 'BSP'))
         or (@ST_TIPO = 'DIS' and (PT.ST_TIPO = 'DIS'))
         or (@ST_TIPO = 'BSPDIS' and (PT.ST_TIPO = 'BSP' or PT.ST_TIPO = 'DIS'))
         or (@ST_TIPO = 'OUT' and (PT.ST_tipo <> 'BSP' and PT.ST_tipo <> 'DIS'))    
        );

-- 
SELECT ID_PESSOA, ST_TIPO
  from BCT_PESSOATIPO as PT
  where 1 =   case when @ST_TIPO is null
                       then 1
                   when @ST_TIPO = 'DIS'
                       then case when PT.ST_TIPO = 'BSP' then 1 else 0 end
                   when @ST_TIPO = 'DIS'
                        then case when  PT.ST_TIPO = 'DIS' then 1 else 0 end
                   when @ST_TIPO = 'BSPDIS'
                        then case when (PT.ST_TIPO = 'BSP' or PT.ST_TIPO = 'DIS') then 1 else 0 end
                   when @ST_TIPO = 'OUT'
                        then case when (PT.ST_TIPO <> 'BSP' and PT.ST_TIPO <> 'DIS') then 1 else 0 end
                   else 0                        
              end;             
go  

DROP TABLE BCT_PESSOATIPO;

Answer 2

In this case I only see the possibility of using a case in the Where clause

this is my version see if it satisfies you

Alter PROCEDURE BCT_SP_PESSOA_BUSCAR_POR_GERENTE
     @ID_GERENTE VARCHAR(50)
    ,@ST_TIPO CHAR(3) = NULL
    ,@NR_CNPJ NUMERIC = NULL
    ,@DS_NOME VARCHAR(100) = NULL
    ,@ID_CODJDE VARCHAR(8) = NULL

AS

    SELECT DISTINCT
         PE.ID_PESSOA
        ,PE.DS_NOME
        ,PE.DS_RAZAOSOCIAL
        ,PE.NR_CPFCNPJ
        ,PT.ST_TIPO
        ,PE.ID_CODJDE
        ,AB.ID_SALESORGANIZATION
        ,PE.ST_CLASSE
        ,PE.ID_CUSTOMERNUMBER
    FROM
        BCT_PESSOA  PE
        INNER JOIN BCT_PESSOATIPO PT ON PT.ID_PESSOA = PE.ID_PESSOA
        INNER JOIN BCT_GRUPOPESSOA GP ON PE.ID_PESSOA = GP.ID_PESSOA
        INNER JOIN BGC_GRUPO_GERENTE GG ON GG.ID_GRUPO = GP.ID_GRUPO
            outer apply (
        select top 1 *
        from BSP_ADDRESSBOOK AB 
        where AB.ID_CUSTOMERNUMBER = PE.ID_CUSTOMERNUMBER
        and AB.ID_SALESORGANIZATION = PE.ID_SALESORGANIZATION
        )
        AB
    WHERE
        GG.ID_GERENTE = @ID_GERENTE
        AND case @ST_TIPO when 'BSP' then PT.ST_TIPO = 'BSP'
        when 'DIS'  then PT.ST_TIPO = 'DIS'
        when 'BSPDIS' then PT.ST_TIPO = 'BSP' and PT.ST_TIPO = 'DIS'
        when 'OUT' then PT.ST_tipo <> 'BSP' and PT.ST_tipo <> 'DIS'
        else PT.ST_TIPO is not null end,
        AND (PE.NR_CPFCNPJ = null OR null IS NULL)
        AND (PE.DS_RAZAOSOCIAL LIKE '%' + null + '%' OR @DS_NOME IS NULL)
        AND (PE.ID_CODJDE = @ID_CODJDE OR @ID_CODJDE IS NULL)
        AND AB.ID_PARTNERFUNCTION = 'SP'
    ORDER BY
         PE.DS_RAZAOSOCIAL