sqlsql-serversap-business-one-di-api

Compiler seems to evaluate every if statement even if conditions not met in SQL


I have a stored procedure of a bit over 300,000 lines, and this is my first time working on something of this size. here is an example of the code I'm writing :

IF (@type = 'commande client')
AND @etat = 'ouvert'
BEGIN
    IF (@tiersd = '')
   AND @tiersf = ''
    BEGIN
        IF (@familled = '')
       AND @famillef = ''
        BEGIN
            IF (@commerciald = '')
           AND @commercialf = ''
            BEGIN
                IF (@articled = '')
               AND @articlef = ''
                BEGIN
                    IF (@afamilled = '')
                   AND @afamillef = ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;
                    ELSE IF (@afamilled != '')
                        AND @afamillef = ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itmsgrpcod BETWEEN @afamilled AND ((SELECT MAX(itmsgrpcod)FROM oitm))
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;

                    ELSE IF (@afamilled = '')
                        AND @afamillef != ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itmsgrpcod BETWEEN ((SELECT MIN(itmsgrpcod)FROM oitm)) AND @afamillef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;

                    IF (@afamilled != '')
                   AND @afamillef != ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itmsgrpcod BETWEEN @afamilled AND @afamillef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;
                END;

                ----------------------------

                ELSE IF (@articled != '')
                    AND @articlef != ''
                BEGIN
                    IF (@afamilled = '')
                   AND @afamillef = ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itemcode BETWEEN @articled AND @articlef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;
                    IF (@afamilled != '')
                   AND @afamillef = ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itemcode BETWEEN @articled AND @articlef
                          AND OITM.itmsgrpcod BETWEEN @afamilled AND ((SELECT MAX(itmsgrpcod)FROM oitm))
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;

                    IF (@afamilled = '')
                   AND @afamillef != ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itemcode BETWEEN @articled AND @articlef
                          AND OITM.itmsgrpcod BETWEEN ((SELECT MIN(itmsgrpcod)FROM oitm)) AND @afamillef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;

                    IF (@afamilled != '')
                   AND @afamillef != ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itemcode BETWEEN @articled AND @articlef
                          AND OITM.itmsgrpcod BETWEEN @afamilled AND @afamillef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;
                END;
   

the problem is that the compiler seems to check inside every if statement even if the conditions are not met and that takes about half an hour so I hope to find a solution to this problem


Solution

  • Yes, as mentioned in the comments, the SQL Server's T-SQL compiler is a real compiler that tries to compile every line just as client language compilers do (some client language compilers have compiler-directives to get around this problem, T-SQL has different techniques and features to achieve the same purpose as detailed below).

    If you only want some lines to be compiled then you will need to use a different approach. Since your stored procedure has 300,000 lines, this seems like a very good idea anyway. Here are some of the techniques that you might consider:

    Be forewarned, anything except the first bullet involves dynamic SQL and requires sufficient SQL expertise to understand the security consequences (SQL injection, etc.) and how to deal with them. Also, the further down this list you go, the more advanced technical skills and SQL knowledge you will need to implement these.