Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bplvid
Starting Member
45 Posts |
 Posted - 2012-07-10 : 23:27:36
|
| 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 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-07-11 : 03:04:27
|
| SELECT *FROM allrecordsview k OUTER APPLY dbo.Split(@Keyword, ',') T WHERE k.username = @usernameAND k.description + k.Name + k.items + k.products LIKE '%' + T.items + '%'--------------------------http://connectsql.com/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-11 : 10:31:55
|
quote:
Originally posted by bplvid
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 coalesce(k.description,'')+ coalesce(k.Name,'')+coalesce(k.items,'') +coalesce(k.products,'') like '%' + T.items + '%' where k.username = @username END
try modification above------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
join with null values not returning values