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
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.