ANSI_NULLS

  • 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?

  • 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