July 12, 2005 at 8:35 am
Hello. I'm having an issue with a query I am trying to write. I am passing in a a parameter for the WHERE clause that is sometimes an integer and sometimes it is NULL. So, my WHERE clause looks similar to this:
WHERE table.col = @varName
This works when I send in a real integer value but fails when I send in a NULL. This is a nullable column and sometimes I will want to search when the column is NULL. I know for a NULL value I am supposed to use IS NULL but then it won't work for a true value. I also know about the option of turning "SET ANSI_NULLS OFF" but, unfortunately, this is not an option for me. Is this any way to structure this query so I can sometimes pass in a value, sometimes a NULL, and it will work?
Thanks so much in advance!!!
Todd
July 12, 2005 at 9:13 am
This is a good start. You can use the search option in the top menu to find more articles like this one :
July 12, 2005 at 9:14 am
July 12, 2005 at 9:37 am
Thanks for the articles, the were interesting but my question still remains. How would I form a query to handle both these situations (null parameter and a true value parameter) at different times?
July 12, 2005 at 9:53 am
WHERE (table.col = @varName or @vanName is null)
and
(table.col2 = @varName2 or @vanName2 is null)
July 12, 2005 at 10:06 am
Yeah, I thought of that as well but the problem is that I need one or the other. Not both.
July 12, 2005 at 10:07 am
If the parameter coming in is null you only want to pull back records where the column has null entries? Or are you always looking to pull back records where the column has null entries, regardless of what's passed in? I'm thinking the former. If that's the situation, you can use a CASE statement in the WHERE clause in conjunction with an ISNULL(). I'm setting the value to -1 but use a value that wouldn't occur:
WHERE CASE @varName IS NULL THEN -1 ELSE @varName END = ISNULL(table.col, -1)
Or, you can handle @varName before this...
SET @varName = ISNULL(@varName, -1)
and then do the WHERE clause test:
WHERE ISNULL(table.col, -1) = @varName
Yes, this causes an index/table scan, but you lose that as soon as you go with the NULL values anyway.
K. Brian Kelley
@kbriankelley
July 12, 2005 at 10:17 am
WHERE
ISNULL(table.col, -1) = @varName
this will kill any possible use of an index on that column. Maybe this article may benefit you :
Dynamic Search Conditions in T-SQL
and this while I'm at it :
July 12, 2005 at 10:18 am
BTW, if yo send one parameter as null, that search condition will be ignored completely the way I wrote it. Can you post some sample data with exemples of the desired results depending on the value of the parameter ??
July 12, 2005 at 10:36 am
Yup, I did say that you will go to scans. Any time you drop to an IS NULL you basically blow away the chance to use an index. That's one of the big reasons to try and build structures that don't allow NULLs. IS NULL and ISNULL() results in a non-SARGable predicate, meaning an index can't be used. So if you have to check the column for NULLs, you're going with table/index scans.
K. Brian Kelley
@kbriankelley
July 13, 2005 at 1:46 am
Simply:
=NULL will only work if you have set explicitly the value NULL to your column
is null will always work because it checks for value NULL (explicitly set, "nothing", not determined...)
Bye
Gabor
July 13, 2005 at 7:30 am
I'm new, but curious.
Would "WHERE table.col = IsNULL(@varName, table.col)" do the trick?
Thanks for your feedback,
David
July 13, 2005 at 7:34 am
Yes. You can read more here :
July 13, 2005 at 8:44 am
That's a good idea. The real problem is that you must find a default that will never be in the table... shouldn't be too hard if you know your data.
July 13, 2005 at 9:04 am
YES! This technique works just like I wanted. Thanks so much!!!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply