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.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|||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)|||I want to be able to search the SSN field for anything. For example:
last four digits 00
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?|||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.|||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|||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|||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|||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.|||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.|||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.|||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|||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.