SQL Server Performance Forum – Threads Archive

How to reduce IF Loops

Hi, I need small help in reducing IF loops.I have to design a search stored procedure where I can get values in different form means user can provide any values in any combination. The combination are as below INVOICENUMBERINVOICEITEMINVOICEQTY
0 0 0
1 0 0
1 1 0
1 0 1 0- indicate nothing is passed
1- indicate something is passed Logic is written as below. /*********START OF CODE*********************/ DECLARE @INVOICENUMBER VARCHAR(10),
@INVOICEITEMVARCHAR(10),
@INVOICEQTY VARCHAR(10) /*************Nothing is passed*****/ SET @INVOICENUMBER=0
SET @INVOICEITEM=0
SET @INVOICEQTY=0 IF @INVOICENUMBER=0 AND @INVOICEITEM=0 AND @INVOICEQTY=0 BEGIN
PRINT ‘INVOICE/INOVICEITEM/INVOICEQTY NOT PRESENT.RETURN ALL’
END ELSE BEGIN
IF (@INVOICENUMBER>0) BEGIN IF (@INVOICEITEM>0 OR @INVOICEQTY>0) BEGIN
PRINT ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT ALONG WITH INVOICE NUMBER.RETURN RECORDS’
END
ELSE
BEGIN
PRINT ‘INVOICE NUMBER RECORDS RETURN ONLY’
END
END ELSE
BEGIN IF (@INVOICEITEM>0 OR @INVOICEQTY>0) BEGIN
PRINT ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT WITHOUT INVOICE NUMBER.RETURN RECORDS’
END
END END GO /********************END OF CODE**********/ Above code works perfectly fine but i need help in reducing IF Loops. Is there is any way i can remove IF Loop ? and achieve above task without using Loops. Note: I dont want to achieve this by using LIKE/CASE or Dynamic queries because all are not good if we consider the performance. Thanks and Regards
Ravi K

---

Check this link for building dynamic seach conditions:
http://www.sommarskog.se/dyn-search.html

---

Declare @msg varchar(200)
SET @msg = ”
SELECT @msg =
CASE WHEN (@INVOICENUMBER=0 AND @INVOICEITEM=0 AND @INVOICEQTY=0) THEN ‘INVOICE/INOVICEITEM/INVOICEQTY NOT PRESENT.RETURN ALL’
WHEN @INVOICENUMBER>0 AND (@INVOICEITEM>0 OR @INVOICEQTY>0) THEN ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT ALONG WITH INVOICE NUMBER.RETURN RECORDS’
WHEN @INVOICENUMBER>0 AND NOT (@INVOICEITEM>0 OR @INVOICEQTY>0) THEN ‘INVOICE NUMBER RECORDS RETURN ONLY’
WHEN NOT (@INVOICENUMBER>0) AND (@INVOICEITEM>0 OR @INVOICEQTY>0) THEN ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT WITHOUT INVOICE NUMBER.RETURN RECORDS’
ELSE’ all good’
END SELECT @msg ***********************
Dinakar Nethi
SQL Server MVP
***********************

---

Not necesarrily true regarding the dynamic queries. If you have multiple combinations of search condition, it’s better to use dynamic query than bunch of IF ELSE conditions. You can set up the SELECT and FROM clause as those are static regardless of the input parameters within the dynamic query. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

The number of IFs is driven by the number of actual conditions you need to handle on the data. I would suggest to focus more on the readability, scalability and maintainability of the code rather than the number of IF statement. I sometimes suggest to specifically imply the IFs for the sake of someone, other than the one who developed the code, can follow the logic of the statement. I’ll let the SQL Optimizer do its work. In your case, I would suggest that you first determine how many conditions are really there since it drives the number of situation you need to handle. For instance, based on your example, if @INVOICE NUMBER > 0 then you ignore other cases (regardless of the values of the other two fields). That means that there are 8 total conditions (using the principle of statistic 2*2*2 = 8, 2 possible outcomes, 1 or 0 for a given field and there are three fields. Yes, i agree that > 0 may not necessarily mean 1 but 1 or more. But for the sake of discussion, let us limit to 0 or 1). Let us look at an illustration below INVOICENUMBER INVOICEITEM INVOICEQTY EQUIVALENT BITWISE VALUE
0 0 0 000 = 0
1 0 0 100 = 4
1 1 0 110 = 6
1 0 1 101 = 5 Disregarding
0 1 1 011 = 3
0 0 1 001 = 1
0 1 0 010 = 2
1 1 1 111 = 7
Note that you still have to handle the specific conditions you want (in this example, 4 conditions) Let us go back to your code,
IF @INVOICENUMBER>0 BEGIN
IF (@INVOICEITEM>0 OR @INVOICEQTY>0)
can be replaced by logical statement of (@NEWVALUE = 011 OR @NEWVALUE = 010 OR @NEWVALUE = 001) in just one IF statement (but still handles the the data condition you want).
Let us consider your code above:
IF @INVOICENUMBER=0 AND @INVOICEITEM=0 AND @INVOICEQTY=0
BEGIN
PRINT ‘INVOICE/INOVICEITEM/INVOICEQTY NOT PRESENT.RETURN ALL’
END
ELSE
BEGIN
IF (@INVOICENUMBER>0)
BEGIN
IF (@INVOICEITEM>0 OR @INVOICEQTY>0)
BEGIN
PRINT ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT ALONG WITH INVOICE NUMBER.RETURN RECORDS’
END
ELSE
BEGIN
PRINT ‘INVOICE NUMBER RECORDS RETURN ONLY’
END
END
ELSE
BEGIN
IF (@INVOICEITEM>0 OR @INVOICEQTY>0)
BEGIN
PRINT ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT WITHOUT INVOICE NUMBER.RETURN RECORDS’
END
END Let us rewrite it into the following: IF (@INVOICENUMBER=0 AND @INVOICEITEM=0 AND @INVOICEQTY=0)
BEGIN
PRINT ‘INVOICE/INOVICEITEM/INVOICEQTY NOT PRESENT.RETURN ALL’
END
ELSE
IF (@INVOICENUMBER>0) AND (@INVOICEITEM > 0 OR @INVOICEQTY>0)
BEGIN
PRINT ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT ALONG WITH INVOICE NUMBER.RETURN RECORDS’
END
ELSE
IF (@INVOICENUMBER>0) AND (@INVOICEITEM = 0 AND @INVOICEQTY = 0) THEN
BEGIN
PRINT ‘INVOICE NUMBER RECORDS RETURN ONLY’
END
ELSE
IF (@INVOICEITEM>0 OR @INVOICEQTY>0)
BEGIN
PRINT ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT WITHOUT INVOICE NUMBER.RETURN RECORDS’
END
The difference is just one ELSE statement!
Now, let us try to compress it more: IF (@INVOICENUMBER=0)
BEGIN
IF (@INVOICEITEM>0 OR @INVOICEQTY>0)
PRINT ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT WITHOUT INVOICE NUMBER.RETURN RECORDS’
ELSE
PRINT ‘INVOICE/INOVICEITEM/INVOICEQTY NOT PRESENT.RETURN ALL’
END
ELSE
BEGIN
IF (@INVOICEITEM > 0 OR @INVOICEQTY>0)
PRINT ‘EITHER INVOICEITEM OR INVOICE QYT PRESENT ALONG WITH INVOICE NUMBER.RETURN RECORDS’
ELSE
PRINT ‘INVOICE NUMBER RECORDS RETURN ONLY’
END
Note the number of condition did not change but somehow, we manage to shrink the code to 3 if statements. If you use the implied logic of the OR together with the preceding statements, you can reduce the number of IFs. But then again, its a bit harder to understand because of IMPLIED conditions/assumptions you need to consider. In the end, i’d sacrifice a little bit of extra IF statement if doing so can facilitate the maintainability of the code. After all, someone has to maintain the code when the original developer is no longer available. And I’d be happy if my developer just spend 1 hour understanding 300 lines of code instead of spending 16 hours in a equivalent code of 50 lines. Hope this helps. May the Almighty God bless us all!
www.empoweredinformation.com

---

Hi, Thank everyone for your inputs. –Compressing IF logic was helpful –Dyanmics SQL, I have tested with dynamics SQL, i can see my stored procedure is taking more time than IF ELSE switch.Hence,using dynamics SQL is rule out.
Thanks and Regards
Ravi K

---

Dynamic SQL is an improvement in most cases, provided that you also use sp_ExecuteSQL, and feed the parameters properly. You could try building the sp_ExecuteSQL call in dynamic SQL as well. But in any case, sp_ExecuteSQL may show improvement over time only, as execution plans get re-used. But if it doesn’t help, it doesn’t help.

---

I agree with Adriaan. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

]]>