how to make query for all kind of search

Question

0.00/5 (No votes)

See more:

i want to make a search app. where user select a specific column for searching then searching take palce in that column now i have 10 column for searching.
Do i need to make 10 different searching query ??? or a general query can work for all.
remember user can select more than one column for searching
suppose
user want to search in
Title
And
Description
And
Location

another user want to search like this
Title
or
Description
or
Location

another want like this
Title
or
Description

another user only want to search in Title

so what i need to do write query separately for each selection. which is really time consuming and tough. Can i make general query which do all kind of searching??

Posted 30-Sep-13 22:21pm

Muhamad Faizan Khan

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)

pradiprenushe · Answer 1 · 2013-09-30T22:41:00

Solution 1

Use dynamic query. Make all parameter from sp nullable so it will not give error if input parameter is not provided.
e.g @Title Varchar(100) = null

then create dynamic query & add optional filter parameter by checking parameter is null or not.

Refer this link
Building Dynamic SQL In a Stored Procedure[^]

Posted 30-Sep-13 22:41pm

pradiprenushe

Comments

Maciej Los 1-Oct-13 5:33am

Good one!
+5!

pradiprenushe 1-Oct-13 5:49am

Thanks

Muhamad Faizan Khan 1-Oct-13 13:19pm

What will i do with AND operation??

pradiprenushe 1-Oct-13 14:13pm

How you are going to decide to use OR/And? What is logic? Or any case

Muhamad Faizan Khan 1-Oct-13 14:24pm

a person want to search a specific work In title and Description. I will provide him and .or option definitely

pradiprenushe 1-Oct-13 14:29pm

I am assuming at a time only one or/and condition will be there while getting result?
e.g
1.Title and description
2.Title or description
If you write query for both. how are you going to decide use 1 OR 2?

Muhamad Faizan Khan 1-Oct-13 14:32pm

off course what user select and or or?

Muhamad Faizan Khan 1-Oct-13 14:33pm

i'll ask user do you want to search more ? if yes then ask how? using or or and??

Muhamad Faizan Khan 1-Oct-13 14:33pm

can i use this approach with your solution??

pradiprenushe 1-Oct-13 14:35pm

Then it will be better to create clause in your code. Send that where clause to stored procedure as parameter.

Muhamad Faizan Khan 1-Oct-13 14:46pm

can tell me how?

Muhamad Faizan Khan 1-Oct-13 14:35pm

you said only make the query with or or and?

pradiprenushe 2-Oct-13 5:47am

CREATE PROCEDURE SPNAME
Declare @WhereQuery nvarchar(max) // pass this parameter from code
AS
BEGIN
Declare @SqlQuery nvarchar(max)
Set @SqlQuery = ' Select * from table Where 1 = 1 '
if(@WhereQuery <> '' && @WhereQuery is not null)
begin
@SqlQuery =@SqlQuery + ' AND ' + @WhereQuery
end
END
// @WhereQuery value may either of this
//1. null or blank
//2. Title or description
//3. Title and description

Mahendran Murugesan · Answer 2 · 2013-09-30T22:58:00

Hi,

Please find the code sample for the search pattern you described.

SQL

/*
drop Table Books
Create Table Books
(
Bookid int identity(1,1), 
Title varchar(150),
Description varchar(150),
Location  varchar(150), 
isactive bit
)

Insert Books values('Around the world','Around the world 80 days', 'Chennai',1)
Insert Books values('Paradise Lost','Paradise lost by John Milton', 'London',1)
Insert Books values('War and peace','Leo Tolstoy', 'Russia',1)
Insert Books values('Chennai Travel Guide','Chennai Travel Guide', 'Chennai',1)
Insert Books values('Introduction to C# ','Microsoft', 'USA',0)


Select * from Books Where Title  = 'Around the world' and Location ='Chennai'
Select * from Books Where Title  = 'Around the world' or Location ='Chennai'
*/

/********* Dynamic Query ***************/
go
Declare @Qry nvarchar(max)
Declare @Title varchar(150)  
Declare @Description varchar(150) , @DescOpr varchar(10)
Declare @Location varchar(150) , @LocationOpr varchar(10)

Set @Title = 'Around the world' 
set @Description = ''
Set @Location = 'Chennai' 
set @LocationOpr = 'or'

Set @Qry =  ' Select * from Books  Where Isactive =1'
if (@Title <> '' ) 
Begin 
set @Qry =   @Qry + ' And Title = '''+ @Title + ''''
End
if (@Description <> '' and  @DescOpr ='AND') 
Begin 
set @Qry =   @Qry + ' And description ='''+  @Description + ''''
End
Else 
Begin 
set @Qry =   @Qry + ' or description ='''+  @Description + ''''
End


if (@Location <> '' and  @LocationOpr ='AND') 
Begin 
set @Qry =   @Qry + ' And Location = '''+ @Location + ''''
End
Else
Begin 
set @Qry =   @Qry + ' or Location = '''+ @Location + ''''
End
print @Qry
exec (@Qry)
go

Hope this helps
Regards,
Mahe...