Hamza El Alamy
Reputation: 21
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
Upvotes: 1
Views: 394
Answers (1)
RBarryYoung
Reputation: 56755
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:
- Break it up into a master stored procedure with all of the decisions (or the higher level ones) and many subordinate stored procedures, that have the actual SQL queries (and/or the lower level decisions)
- Dynamic SQL for the lower levels. Dynamic SQL is not compiled until it is explicitly called at run-time (the SSMS scripter uses techniques like this, so you can look to it for examples, another example are catch-all queries)
- Use table-driven code-generation to automatically create the proper SQL code as a pre-compiler stage, implementing one or both or the preceding techniques
- Use table-driven code-generation to dynamically create only the SQL code that you need at run-time then dynamically execute it
- Any or all combinations of the preceding techniques
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.
Upvotes: 1
Related Questions
- 'IF' does not prevent error, but only if it has already executed
- SQL is trying to execute statements enclosed in an IF block even when the condition is not satisfied
- Why does SQL Server skip over an IF statement?
- IF condition being checked in SQL even if it doesn't match
- IF statements in SQL Server triggers
- T-sql: Cannot see why IF clause never gets called
- Why won't oracle sql developer recognise my if statement
- T-Sql appears to be evaluating "If" statement even when the condition is not true
- SQL if statement debugging
- Why is variable declared inside IF statement created even when condition evaluates to false?