搜索结果存储过程 [英] Search Result stored procedure

问题描述

我有2个文本框，一个用于资源，另一个用于位置。



到目前为止我做的是创建用于显示搜索结果的存储过程。我的问题是在提供资源的同时获取相应的资源，但也采取与位置匹配的其他资源



例如，如果我在印度搜索沙龙它显示的是结果是在位置（印度），还显示医院，学校等..



假设：



文本框1：沙龙文本框2：印度



如果我这样给出我的搜索结果



搜索结果

I have 2 text boxes one is for resource and another one is for location.

As of now what i did was i create stored procedure for displaying search results. My problem is while giving resource it s fetching the corresponding resource but also taking other resource that match with location

for example if i searched for saloon in India it is displaying a the result which is in the location( India ) and also display hospital,school etc..

assume that:

textbox 1: Saloon textbox 2: India

if i give like this my search results coming

search result

Resource : Naturals - India's No: 1

Location : India

Resource : Apollo Hospitals

Location : India

Resource : Sahyadri School

Location : India

怎么解决这个？



存储过程是

how to solve this ??

stored procedure is

@SEARCH NVARCHAR(100) = NULL,
@loc NVARCHAR(100) = NULL
as
select *
from tblBusinessCategory as b
    inner join tblUser as u on b.BusinessID=u.BusinessCategoryId
    inner join tblAddress as a on u.AddressId=a.AddressID

where a.City = @loc OR a.State = @loc
       OR b.BusinessName LIKE '%' + @SEARCH + '%'
Order By case when b.BusinessName = @SEARCH then 0 else 1 end

推荐答案

仔细看看你的where子句。你有3个OR。当然，当任何条件满足时，它都会匹配。



你需要类似的东西：

Look carefully at your where clause. You have 3 OR. So of course it is going to match when any of those conditions when true.

You need something like:

where (a.City = @loc OR a.State = @loc)
       AND b.BusinessName LIKE '%' + @SEARCH + '%'

它与您有关，就像您从城市和州或地点名称获取结果一样。因此，它可以是城市a中的位置a或学校的沙龙。

It has to do with you using like your are getting results from a city and state or a location name. Therefore it can be a saloon in location a or school in city a.

这篇关于搜索结果存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！