Storage procedure with case

1

I have this Stored Procedure where I need to search by maternal or paternal surname

Bearing in mind that in my grid both concatenated are shown as surnames, as is in the select

But doing it with the case complicates me since I can not have the two fields in a single condition:

ALTER PROCEDURE [dbo].[IN_obtenerEmplSucEmp1]
    @Rut varchar(18),   
    @Apellidos varchar(100), 
    @codsuc INT,
    @codemp INT

AS

SELECT 
        per_rut_per, 
        per_nom_per, 
        (per_ape_pat+' '+per_ape_mat) AS Apellidos, 
        per_anx_per, 
        per_cor_ele,
        par1.par_des_par as per_emp_per, 
        par2.par_des_par as per_suc_per 
    FROM PER 
    INNER JOIN PAR as par1 ON PAR1.PAR_COD_TAB = 18  AND par1.PAR_COD_PAR = PER_emp_PER
    INNER JOIN PAR AS PAR2 ON PAR2.PAR_COD_TAB = 13  AND PAR2.PAR_COD_PAR = per_suc_per

    where   per_rut_per = CASE @Rut WHEN '' THEN per_rut_per ELSE @Rut END AND
            per_ape_pat like CASE @Apellidos WHEN '' THEN per_ape_pat ELSE @Apellidos+'%' END and   
            per_suc_per = CASE @codsuc WHEN 0 THEN per_suc_per ELSE @CODsuc END and
            per_emp_per = CASE @codemp WHEN 0 THEN per_emp_per ELSE @CODemp END 

How can I solve it?

    
asked by Ricconter Salazar 19.12.2016 в 22:04
source

1 answer

1

If you mean that you want to search for en el WHERE by maternal or paternal surname, instead of the CASE clause , you can modify your query in this way:

WHERE (per_ape_pat LIKE '%'+@Apellidos+'%' OR per_ape_mat LIKE '%'+@Apellidos+'%')
    
answered by 19.12.2016 в 22:39