I want run a big SELECT query that can take 20 filtering criteria where each criteria can have multiple values and can also be NULL.
The way I'm doing it presently is by generating the SQL query on the fly depending on the filtering criteria that I need.
However, I'm rewriting the whole thing in asp.net and I'm trying avoid writing the SQL query on the fly. I tried to program it as Table-Valued Parameters, but it runs much slower than the old way and I'm can't figure out why.
Am I shooting myself in the foot here? What's the best practice for passing multiple arguments to a stored procedure?
Here's the TVP:
CREATE TYPE SpecTangoCandidatListe AS TABLE
(
cboActifs BIT, cboCrochet BIT, cboEtoile BIT, cboExcl BIT,
cboIntero BIT, anses CHAR(5), cegep CHAR(2), tour TINYINT,
chprog BIT, prog CHAR(5), cpt BIT, decision CHAR(2),
entente CHAR(1), equiv CHAR(1), autSej TINYINT,
typCit TINYINT, [message] VARCHAR(10), filter VARCHAR(50),
sort VARCHAR(25), dir VARCHAR(5)
);
I'm passing the TVP as @filter to the stored procedure:
ALTER PROCEDURE [dbo].[tango_api_candidats_liste]
@filtre SpecTangoCandidatListe ReadOnly
AS
BEGIN
SELECT (huge number fields)
FROM (many tables)
JOIN @filtre f ON
(PE.FK_COL_NO = f.cegep OR f.cegep IS NULL)
AND (DAD_ANSES = f.anses OR f.anses IS NULL)
AND ((DAD_ACTIF = f.cboActifs AND (T.COD_NO IS NULL OR LEFT(T.COD_NO,1)!='0')) OR f.cboActifs IS NULL)
AND (PRF_ICON_CROCHET = f.cboCrochet OR f.cboCrochet IS NULL)
AND (PRF_ICON_ETOILE = f.cboEtoile OR f.cboEtoile IS NULL)
AND (PRF_ICON_EXCL = f.cboExcl OR f.cboExcl IS NULL)
AND (PRF_ICON_INTERO = f.cboIntero OR f.cboIntero IS NULL)
AND (TOU_NO = f.tour OR f.tour IS NULL)
AND (DAD_CHANG_PROG = f.chprog OR f.chprog IS NULL)
AND (PRO_NOPRO = f.prog OR f.prog IS NULL)
AND (SUBSTRING(DAD_CODPERM,11,1)='T' AND f.cpt = 1 OR f.cpt IS NULL)
AND ( (LEFT(T.COD_NO,1)='A' AND f.decision = 'A') OR
(LEFT(T.COD_NO,1)='D' AND f.decision = 'D') OR
(ISNUMERIC(T.COD_NO)=1 AND f.decision = 'R') OR
(T.COD_NO='AT' AND f.decision = 'T') OR
(COALESCE(T.COD_NO,'')='' AND f.decision = 'X') OR
f.decision IS NULL
)
AND ( (TOU_PROCESSUS = 1 AND f.entente='a') OR
(FK_TYF_NO IS NULL AND f.entente='0') OR
(FK_TYF_NO = f.entente) OR
f.entente IS NULL
)
AND ( DAD_EQE_COMPLET = f.equiv OR f.equiv IS NULL )
AND ( DAD_AUTSEJ = f.autSej OR f.autSej IS NULL )
AND ( DAD_TYPCIT = f.typCit OR f.typCit IS NULL )
AND ( DAD_NOM LIKE f.filter OR
DAD_PRENOM LIKE f.filter OR
DAD_CODPERM LIKE f.filter OR
D.DAD_NO LIKE f.filter OR
DAD_COURRIEL LIKE f.filter OR
DAD_TELE_RES LIKE f.filter OR
CPA_NOMPAYS LIKE f.filter OR
COM_CONTENU LIKE f.filter OR
f.filter IS NULL
)
END