0

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
Guillaume Filion
  • 480
  • 2
  • 6
  • 13
  • 1
    This sounds a lot like a catch-all query to me. Check out this article on doing this type of query. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ – Sean Lange Apr 03 '17 at 19:42
  • Thanks, I didn't know about catch-all queries. It does look like what I'm trying do to, I'll check the linked article. – Guillaume Filion Apr 03 '17 at 19:56
  • Thanks again for the pointer, from it I got to this page that answered all my questions. http://www.sommarskog.se/dyn-search-2008.html – Guillaume Filion Apr 04 '17 at 15:42

0 Answers0