Query

Question

Query

jagjitsingh

Ten Centuries

Points: 1059
More actions
December 16, 2021 at 8:37 am

#3964295
Hi
How the where condition works in below code. How IsNull & NullIf works.
SELECT *
FROM tblName
WHERE [ColumnName] = ISNULL(NULLIF(@parameter, ''), [ColumnName])
Thanks
- This topic was modified 2 years, 11 months ago by jagjitsingh.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

That's an odd construct. NULLIF compares two values. If they're matching, it returns null. Otherwise, it returns the first value, in this case, @parameter. ISNULL checks the first value to see if it's null, and then returns the second value if it is.

So, what we have here is, if the parameter passed is an empty string (compared to '') it sets the value to NULL. Then, it compares the column to the column, in short, return everything if the string is empty, otherwise, just return the stuff that matches the parameter value. It's a bit of an odd construct. Plus, performance is going to absolutely stink since statistics and indexes can't be used due to the calculations on the values passed. You'll always get scans.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

This follows the same pattern as a "Catch-All" query and Grant is totally correct... both performance and resource usage, even for a small table, is going to suffer a lot compared to doing things the right way.

Please see the following article for how to do it the right way in what I advertise as being the definitive article on the subject written by Gail "Gila Monster" Shaw.

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

And notice that Gail's method is NOT subject to SQL Injection. You have to be careful there. It's easy to "get lazy" and do it the wrong way. It's just as easy to do it the right way but you have to pay attention when you're reading the article. It'll become a "natural" habit in the future after you've practiced it a couple of times.

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