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?