Search Result stored procedure

Ruga Lincy

11y912

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Subhani Shaikh11y ago
@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 Ahmed11y ago
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(@SEARCH,0) then 0 else 1 end
Ruga Lincy11y ago
if give resource in textbox withot givng location i need to dispaly all record corresponding to that resource .. how to do this ?
Subhani Shaikh11y ago
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