Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Posts
    122

    Unanswered: Nulls in stored procedure

    I have a stored procedure that is using Like and a parameter to query by Social Security Number. Not all of my records have SSN's, so some are null.

    When I run the stored procedure and use the "%" to show everything, it does not show any records that are null. I have tried to use the IsNull funtion to give the null values a value of 'none', but that did not help.

    Any help would be appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69

    Re: Nulls in stored procedure

    Originally posted by shearness
    I have a stored procedure that is using Like and a parameter to query by Social Security Number. Not all of my records have SSN's, so some are null.

    When I run the stored procedure and use the "%" to show everything, it does not show any records that are null. I have tried to use the IsNull funtion to give the null values a value of 'none', but that did not help.

    Any help would be appreciated.
    assign default value % to the parameter

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you want to display records with NULL SSN (even though they should not be, unless I didn't get your requirements right) then just do ISNULL(SSN, @SSN)

  4. #4
    Join Date
    Sep 2003
    Posts
    122
    I want to be able to search the SSN field for anything. For example:
    last four digits %0000
    entire field 000-00-0000
    or
    if I don't know the social, I want to be able to use just % to display all social security #'s, even those with null values.

    Does this make sense?

  5. #5
    Join Date
    Sep 2003
    Posts
    122
    Also, I have tried both of your posts and I am having the same problem. When the message box pops up asking for me to specify a value for SSN, I enter %, which in theory should display all records. Instead, it is only displaying records that do not have a null value for SSN. How do I get the % to include even those that have null values? Thanks in advance for any help.

  6. #6
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Originally posted by shearness
    Also, I have tried both of your posts and I am having the same problem. When the message box pops up asking for me to specify a value for SSN, I enter %, which in theory should display all records. Instead, it is only displaying records that do not have a null value for SSN. How do I get the % to include even those that have null values? Thanks in advance for any help.
    I think now I got it. I guess some of the records's SSN are NULL. If that is correct those records will be elimated no matter what you specify in the where clause.

    TRYING USING

    SELECT CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
    ELSE SSN
    END

    FROM TABLE1
    WHERE
    (
    CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
    ELSE SSN
    END) LIKE 'PARAM1%'

    THIS IS NOT A COMPELE CODE. You have do decalare a variable and use it in the like operator

    Hope it helps

  7. #7
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Originally posted by smasanam
    I think now I got it. I guess some of the records's SSN are NULL. If that is correct those records will be elimated no matter what you specify in the where clause.

    TRYING USING

    SELECT CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
    ELSE SSN
    END

    FROM TABLE1
    WHERE
    (
    CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
    ELSE SSN
    END) LIKE 'PARAM1%'

    THIS IS NOT A COMPELE CODE. You have do decalare a variable and use it in the like operator

    Hope it helps
    Sorry for my typo errors in the previous reply

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    if @PARAM1 is null
    set @PARAM1 = '%'
    else
    set @PARAM1 = '%' + ltrim(rtrim(@PARAM1)) + '%'

    SELECT CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
    ELSE SSN
    END

    FROM TABLE1
    WHERE ISNULL(SSN, @PARAM1) LIKE @PARAM1

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Mind you though, that using this approach may yield very undesireable results in terms of performance, because you may be returning ALL the rows in the table. Think about limiting your search range to a reasonable number of rows.

  10. #10
    Join Date
    Sep 2003
    Posts
    122
    I'm not able to use either of your code in my stored procedure. I'm get errors whenever I try to input it and save it. I'll admit, I'm new to this function, but I'm really stumped, it seems easy enough. Please help me draft this stored procedure so I can leave you alone.

  11. #11
    Join Date
    Sep 2003
    Posts
    122
    Okay, I've got the code in correctly, but I have one more question. I am able to view all entries now when I use %. But now, I am unable to limit the search to whatever I specified in the parameter box. How do I get the best of both worlds? Again, I want to be able to input any of the following in the parameter box and have the results:

    % shows all records
    321% shows all records with SSN that start with 321 only
    321456789 shows only the one record with that SSN

    Thanks again for all of your help.

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you choose to control the contents of @PARAM1 in the front-end, then the following should work:

    SELECT CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
    ELSE SSN
    END

    FROM TABLE1
    WHERE ISNULL(SSN,
    case when datalength(@PARAM1) != 1 then SSN else @PARAM1 end
    ) LIKE @PARAM1

  13. #13
    Join Date
    Sep 2003
    Posts
    122
    That's got it. I would ask for an explanation of what everything means, but you have done plenty. Thank you again for all of your help. That is exactly what I wanted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •