Search based on Priority queue

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i need some help here.. am trying to implement a search result

conditions

1# it should fetch records that equals what keyword user enter on textbox(@SEARCH)

2# Along with the first condition It also fetch the matched records which is entered on textbox(@SEARCH)

3# What keyword searched that should show 1st and other matches will show after that( what i means priority queue)

4# It should check @loc paramater and fetch to that keyword.

SQL

@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 b.BusinessName = @SEARCH and
a.City = @loc
       OR a.State = @loc
       and  b.BusinessName LIKE '%' + @SEARCH + '%'

Posted 19-Aug-15 22:51pm

Member 11367931

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-08-19T23:13:00

Solution 1

If i understand you well...
Replace this:

SQL

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

with:

SQL

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

unless you want to fetch data when one of possible conditions is meet:

SQL

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

[EDIT]
As to the OP's comments... i would suggest to create dynamic queries. See:
Building Dynamic SQL In a Stored Procedure[^]
Using Dynamic Queries[^]

[EDIT 2]
Assuming that you want to display data in ascending order, you have to add ORDER BY clause:

SQL

SELECT *
FROM TableName
WHERE <condition>
ORDER BY FieldName

Posted 19-Aug-15 23:13pm

Maciej Los

Updated 20-Aug-15 2:43am

v4

Comments

Member 11367931 20-Aug-15 5:27am

If i give keywords in both the text boxes the result is fetching correctly. But if i gave the keyword in textbox(@search) alone the result is not fetching

Maciej Los 20-Aug-15 5:43am

Did you ever heard about dynamic queries?

Member 11367931 20-Aug-15 5:44am

No sir

Maciej Los 20-Aug-15 5:45am

See updated answer (after edit).

Member 11367931 20-Aug-15 6:04am

am dead at this Stored Procedure.. can u help me sir ??

Member 11367931 20-Aug-15 6:47am

is this possible to set Priority queue.. Example: i have arun, arunkumar,arunraj etc in my table. if i search arun i want to display arun 1st then arunraj, etc..

if i search arun it fetch arunraj 1st and then arunkumar then arun..

do u understand my problem ??

Maciej Los 20-Aug-15 8:38am

Assuming that you want to display data in ascending order, you have to add ORDER BY clause to your query.

Member 11367931 20-Aug-15 9:11am

thanks

Maciej Los 20-Aug-15 9:21am

You're very welcome. Cheers, Maciej

Member 11367931 · Answer 2 · 2015-08-20T17:25:00

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 a.City = @loc OR a.State = @loc
       OR b.BusinessName LIKE '%' + @SEARCH + '%'
Order By case when b.BusinessName = @SEARCH then 0 else 1 end

this is what i need..