Using an Integer parameter in a WHERE clause

Question

Post reply

Using an Integer parameter in a WHERE clause

RedSoxRay

SSC Enthusiast

Points: 194
More actions
July 7, 2021 at 11:25 pm
Go to Answer
#3905534

I have a simple query which has one parameter (@Amount); if I use it like the pasted code below, it works just fine:
DECLARE
@Amount int,
@Client varchar(500)
SET @Amount = '1170'
SET @Client = 'Google'
Select * from Invoices
where Client like '%' + @Client + '%'
and InvoiceAmount = @Amount
But what if I want the ability to only populate the @Client parameter, but leave the @Amount blank...in other words, bring back all of Google's Invoices, no matter what the InvoiceAmount is, how would I go about doing that?
Thanks In Advance for your help.
NBSteve

SSCarpal Tunnel

Points: 4053
More actions
July 14, 2021 at 5:24 pm
Answer
#3907935
Just want to point out that while an OR clause is the simplest way to solve for this, it is not always the best way.
If you have an index on the field in the OR clause (i.e. invoiceamount, in this example), the OR construct will likely prevent that index from being used in a seek, which could have a very negative performance impact. In that case, you might want to consider using 2 different queries, one with the @amount parameter and one without, and running the appropriate version via conditional logic or dynamic SQL with each execution.
You may also see people use NULL replacement with something like:
```
where Client like '%' + @Client + '%' 

and InvoiceAmount = ISNULL(@Amount, InvoiceAmount)
```
This is very similar to the "OR" solution and can also prevent your indexes from being used.
If you want more explanation about structures that can prevent indexes from being used, search for "sargability".

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16152 More actions · Answer 1

that's a basic OR statement -

and (invoiceamount = @amount or @amount is null)

RedSoxRay SSC Enthusiast Points: 194 More actions · Answer 2

Thanks very much for both of your responses. I didn't take performance into account, but NBSteve gave me some good insight.

Thanks again!

Jeff Moden SSC Guru Points: 1004300 More actions · Answer 3

Actually, both OR and ISNULL are horrible for performance and should be avoided... when I say "should be avoided", I'm very close to saying "must be avoided at all costs" because of the troubles I've seen caused by both. And small row counts aren't a reasonable justification because you're still wasting resources that don't need to be wasted. There is an exception that can help and I talk about that below in "the second article" reference.

Yep. It takes some thought and a little extra time to write the code to avoid both and more.

First, this type of query is "lovingly" referred to as a "Catch All Query" by many of the big dogs in the business. Two of the best articles ever on the subject were both written by Gail Shaw. Here's a link to her articles. My basic advice is that if you're not doing it the way she depicts, then you're doing it the wrong way and good luck with your performance. Sometimes you can "get lucky" but usually not.

Here's the link to her first article, which covers the "Dynamic SQL Method", which is still totally awesome even 'til this day.

https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

That article starts off with a link to her updated article on the subject. Basically, the first article covers the more complicated but seriously effective dynamic sql method and the second article boils down to the thoughtful use of RECOMPILE. I personally don't recommend that method as a "panacea" because I have seen code that takes 22 seconds to recompile every time it's used and that's well beyond my personal 5 second max for any front end code to return. I normal shoot for something measure in the very low milliseconds to allow for transmission time, etc, etc.

If it's a "couple of times a day" batch job for something like a report, then the use of RECOMPILE is just that the doctor ordered because you can get almost completely stupid with bad practices like OR or ISNULL and it'll still fix them for ya. It won't help at all with datatype mismatches or formulas on columns, though.

Seriously... go read her articles top to bottom. They're both pretty long (especially the second one) but they give you a Phd in the art of writing "Catch All Queries". Just remember to check for compile times if you do end up using the RECOMPILE method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RedSoxRay SSC Enthusiast Points: 194 More actions · Answer 4

Thanks for the reply Jeff. I will read those articles; I appreciate the advice.