Help Needed - ISNULL when the field is already null..

Hello,
I just noticed today, after a long time, that if you do a
SELECT * FROM Table WHERE Field = Field
And that Field is null, it will return NOTHING at all...
Problem is, in my search stored procedures, I always use:
SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
So if the value is null, it simply avoid the check.
But now I'm screwed, for the first time I have to do this on fields that CAN
be null... and well, it returns NOTHING.
of course, WHERE Field IS NULL works like a charm, but I need this to be
working so if it's null, it skips the filter, if not, it does it.
Any help would be appreciated.Forgot to add...
the algorithm should be...
If null then don't check at all, return null and not null
but if not null, then must be it and only it...
I'm saying this, because I tried ISNULL(Field, 'foo') = ISNULL(@.Field,
'foo') and that, well, will only return rows with null values :-(
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> a crit dans le
message de news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||Try,
...
where colA = @.par1 or @.par1 is null
...
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Nicolas LeBlanc" wrote:

> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that C
AN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>
>|||Read this article, which contains lots of info about doing this and similar
operations:
www.sommarskog.se/dyn-search.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||How about :
CREATE TABLE [test] (
[col1] [int] IDENTITY (1, 1) NOT NULL ,
[col2] [datetime] NULL
) ON [PRIMARY]
GO
col1 col2
-- ---
1 2005-02-17 22:15:22.263
2 2005-02-15 22:15:24.920
3 2005-02-14 22:16:23.857
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
declare @.d datetime
set @.d = null -- try this one or the next
set @.d = '2005-02-17 22:15:22.263'
SELECT *
FROM test
WHERE
(col2 = @.d)
UNION ALL
SELECT *
FROM test
WHERE
(@.d IS NULL and col2 IS NULL)
Check execution plans to see which ones are optimized the best:
David Gugick
Imceda Software
www.imceda.com|||Based on your needs, you must use one of the two following statements:
Where (Field = @.Field)
Where @.Field is Null OR (Field = @.Field)
Where (@.Field is Null and Field is Null) OR (Field = @.Field)
Where (@.Field is Null and Field is Not Null) OR (Field = @.Field)
There is also an option in SQL-Server that will make the equality (Null =
Null) return True instead of False but I don't suggest using it; otherwise
you will have a continuous moving target.
S. L.
"Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that
> CAN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.
>|||Personally -- if I can get away with it security-wise -- I would rather use
dynamic SQL to solve these kinds of issues...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uYbp28dFFHA.2052@.TK2MSFTNGP09.phx.gbl...
> How about :
> CREATE TABLE [test] (
> [col1] [int] IDENTITY (1, 1) NOT NULL ,
> [col2] [datetime] NULL
> ) ON [PRIMARY]
> GO
> col1 col2
> -- ---
> 1 2005-02-17 22:15:22.263
> 2 2005-02-15 22:15:24.920
> 3 2005-02-14 22:16:23.857
> 4 NULL
> 5 NULL
> 6 NULL
> 7 NULL
> 8 NULL
> declare @.d datetime
> set @.d = null -- try this one or the next
> set @.d = '2005-02-17 22:15:22.263'
> SELECT *
> FROM test
> WHERE
> (col2 = @.d)
> UNION ALL
> SELECT *
> FROM test
> WHERE
> (@.d IS NULL and col2 IS NULL)
>
>
> Check execution plans to see which ones are optimized the best:
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Seems about right. I wonder why I didn't think of that one before...
I sure won't go into dynamic sql, no way!
Merci !
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> a
crit dans le message de news:Ozl8y$dFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Based on your needs, you must use one of the two following statements:
> Where (Field = @.Field)
> Where @.Field is Null OR (Field = @.Field)
> Where (@.Field is Null and Field is Null) OR (Field = @.Field)
> Where (@.Field is Null and Field is Not Null) OR (Field = @.Field)
> There is also an option in SQL-Server that will make the equality (Null =
> Null) return True instead of False but I don't suggest using it; otherwise
> you will have a continuous moving target.
> S. L.
> "Nicolas LeBlanc" <nicolas_leblanc@.nospamhotmail.com> wrote in message
> news:%23VrpzvdFFHA.3312@.TK2MSFTNGP15.phx.gbl...
>|||If there is only one predicate, then the simplest solution would be:
If @.Field IS NULL
SELECT ... FROM Table
Else
SELECT ... FROM Table WHERE Field = @.Field
Of course with more predicates this approach becomes unwieldly.
Gert-Jan
Nicolas LeBlanc wrote:
> Hello,
> I just noticed today, after a long time, that if you do a
> SELECT * FROM Table WHERE Field = Field
> And that Field is null, it will return NOTHING at all...
> Problem is, in my search stored procedures, I always use:
> SELECT ... FROM Table WHERE Field = ISNULL(@.Field, Field)
> So if the value is null, it simply avoid the check.
> But now I'm screwed, for the first time I have to do this on fields that C
AN
> be null... and well, it returns NOTHING.
> of course, WHERE Field IS NULL works like a charm, but I need this to be
> working so if it's null, it skips the filter, if not, it does it.
> Any help would be appreciated.