Good afternoon.
I have a procedure, in it I have several filters.
one of these filters is @ST_TIPO
-When I get by BSP Parameter I want to filter only by PT.ST_TIPO = 'BSP'
-When I get by Parameter ' DIS ' I want to filter only by PT.ST_TIPO = 'DIS'
-When I get BSPDIS I want to filter by pt.ST_TIPO = 'BSP' or 'DIS'
-When I get OUT I want to filter PT.ST_tipo <> 'BSP' and PT.ST_tipo <> 'DIS'
I can not make an IF @ST_TIPO = 'BSP_DIS'
Below is 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