January 20, 2005 at 1:41 pm
I'm having problems searching on fields with NULL values. Can someone please look at the below code and tell me what's going on?
-- Create a table
create table Table1
(x varchar(10),
y varchar(10))
-- Insert some values
insert Table1 (x,y)
values (null, 'y1')
insert Table1 (x,y)
values ('x1', null)
insert Table1 (x,y)
values (null, null)
-- Set ansi_nulls off and crate sp because I want NULL = NULL to be TRUE.
go
set ansi_nulls off
go
create procedure Table1Search
@x varchar(10) = null,
@y varchar(10) = null
as
select x,y
from Table1
where x = isnull(@x, x) and
y = isnull(@y, y)
order by
x,y
go
set ansi_nulls on
go
--execute the sp with no parameters.
exec Table1Search
I expect to get 3 rows back, but I'm not getting any. Can someone please help me understand why?
January 20, 2005 at 1:59 pm
Since the parameters and the table values could both be NULL, you need to convert each into something you can compare with. So the following syntax works.
select x,y
from Table1
where isnull(x,'') = isnull(@x, '') or
isnull(y,'') = isnull(@y, '')
order by
x,y
Also using AND in the where clause would've only returned the one row where both X and Y are NULL.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply