Geek Answers Handbook

Circuit Breaker / If in condition where

I am trying to change the conditions of a PART where based on some value. I reviewed similar articles here, including:

SQL Switch / Case in the where section

But they do not quite understand in my case.

I have a stored procedure that selects based on its inputs. These inputs are optional (or have default parameters, as they prefer to call it).

The 2 indicated parameters are numbers.

  • If only the first number is specified, then column X must equal that number.
  • if the first and second numbers are indicated, then column X should be> = first AND <= second.
  • If only the second number is specified, it is INCORRECT.

This is what I tried (which obviously didn't work)

DECLARE @SECOND INT; DECLARE @FIRST INT; SET @FIRST = 123456; SET @SECOND = 67890; SELECT * FROM BANK_DETAIL WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES CASE WHEN @SECOND IS NULL THEN X = @FIRST ELSE X >= @FIRST AND X <= @SECOND END -- POSSIBLY SOME MORE WHERE CLAUSES ORDER BY X

REALLY it seems that he needs IF / ELSE, not CASE, but I was directed to CASE .....

Oh, this is MS SQL> = 2005

+4
source share
5 answers

Try setting up @SECOND invalidation instead of using CASE.

 SELECT * FROM BANK_DETAIL WHERE -- other conditions go here AND ((@SECOND IS NULL AND X = @FIRST) OR (@SECOND IS NOT NULL AND X >= @FIRST AND X <= @SECOND))
+4
source 
 SELECT * FROM BANK_DETAIL WHERE (@SECOND IS NULL AND X = @FIRST) or (X >= @FIRST AND X <= @SECOND)

although this would be more efficient:

 IF @SECOND IS NULL BEGIN SELECT * FROM BANK_DETAIL WHERE X = @FIRST END ELSE BEGIN SELECT * FROM BANK_DETAIL WHERE X >= @FIRST AND X <= @SECOND END
+3
source

I would use the logical operators:

 SELECT * FROM BANK_DETAIL WHERE ((@SECOND IS NULL AND X = @FIRST) OR (@SECOND IS NOT NULL AND X >= @FIRST AND X <= @SECOND));
+2
source

You create a dynamic search condition. By forcing a single statement covering both cases, you reduce the parameters of the optimizer. The generated plan should work in both cases when @seconds is null and when it is not zero. You will be much better off using two separate operators:

 IF @SECOND IS NULL THEN SELECT * FROM BANK_DETAIL WHERE X = @FIRST ORDER BY X ELSE SELECT * FROM BANK_DETAIL WHERE X >= @FIRST AND X <= @SECOND ORDER BY X

You intuition, to "simplify" into one operator, leads you along the wrong path. The result is less text, but much longer lead time due to suboptimal plans. The article at the beginning of my answer is covered in detail in this topic.

+1
source

The problem is that CASE returns a value, this is not a branch in the logic. The link OMG offers is pretty much an authoritative source on this (almost everything from Erland Sommarskog would be good advice).

Short summary of the link:

You can use dynamic SQL, where you create a statement based on conditions. This may often be the most effective, but there are disadvantages. One of the biggest weaknesses is potential security issues, so make sure you fully understand the SQL injection attacks and how to prevent them.

Another approach is to use complex logic in your WHERE statement using ORs. In your case, it will be something like below. This approach is slightly simpler than dynamic SQL and safer, but performance may not always be great. If performance is satisfactory for your situation (check it), stick to this approach.

 SELECT * -- I assume that you used * just as an example and don't actually use this in your production code FROM Bank_Detail WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES (@second IS NULL AND X = @first) OR (@second IS NOT NULL AND (x >= @first AND x <= @second)) -- POSSIBLY SOME MORE WHERE CLAUSES ORDER BY x

Another way to organize the expression that I just started ...

 SELECT * FROM Bank_Detail WHERE -- POSSIBLY SOME OTHER WHERE CLAUSES x >= @first AND x <= COALESCE(@second, @first) -- POSSIBLY SOME MORE WHERE CLAUSES ORDER BY x

I have not tested it yet, but I think it would be logically equivalent and could give you a more consistent query plan.

Erland also provides a couple of other possible approaches, so be sure to read his full article on this subject.

0
source

More articles:


All Articles