join with null values not returning values

Question

I've created a view which grabs all the datas from multiple tables and I need to do a keywordsearch on it.Below is my SP where I join my View and  keyword function which does the search on description,name,items,products.My issue here is if items  has null value or products have null value it dosen't return the rows.I need to show the result even if item or products is null.I tried using left outer join,it shows all the rows irrelevant to my search keyword.I tried different approachs like using    where clause  items is null and products is null,no luck.I need some expert advice to handle this.

ALTER PROCEDURE [dbo].[keywordsearch]
 (
 @username varchar(256),
 @keyword varchar(250)
 
)
 
 AS
 
 BEGIN
 
 select * from allrecordsview k join dbo.Split(@Keyword, ',')T on k.description+k.Name+k.items +k.products like '%' + T.items + '%'  where k.username = @username 
 
END

 

Answer 1

Perhaps you want to do it differently, e.g.

T.Items IN (K.Description, K.Name, K.Items, K.Products)

(If you want any of the field to match your keyword). If you want to use like for each of the field, then try

(k.description like '%' + T.Items + '%' or k.name LIKE '%' + T.Items + '%')

etc.

Finally, if you want to use your original query, you can do

COALESCE(K.Description,'') + coalesce(K.Name,'') + ... LIKE '%' + T.Items + '%'

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hello,

If you concate a NULL value with an other value it results in a NULL value again. Comparing a NULL value with an other value results in an undefined result (here "false").

So you have to convert NULL values into a defined value using e.g. the ISNULL function to convert to an empty string like:

on ISNULL(k.description, '') 
   + ISNULL(k.Name, '')
   + ISNULL(k.items, '')
   + ISNULL(k.products, '')
   like 
   '%' + ISNULL(T.items, '') + '%'  
where k.username ...

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing