Search Result stored procedure

Question

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

Subhani Shaikh · Answer

@SEARCH NVARCHAR(100) = NULL @loc NVARCHAR(100) = NULL as declare @sql varchar(max) set @sql = '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 b.BusinessName LIKE '%' + saloon + '%'' if(@loc '') set @sql= @sql + 'AND (a.City = @loc OR a.State = @loc) Order By case when b.BusinessName = @SEARCH then 0 else 1 end' else set @sql = @sql EXEC @sql

Khan Abrar Ahmed · Answer

Hi try this @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 (@loc is null or (a.City = @loc OR a.State = @loc) ) OR (@SEARCH is null or b.BusinessName LIKE '%' + @SEARCH + '%') Order By case when b.BusinessName = isnull(@SEARCH0) then 0 else 1 end

Ruga Lincy · Answer

if give resource in textbox withot givng location i need to dispaly all record corresponding to that resource .. how to do this ?

Subhani Shaikh · Answer

Hi Use and where using or like below 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) AND b.BusinessName LIKE '%' + saloon + '%' Order By case when b.BusinessName = @SEARCH then 0 else 1 end