Advanced Search Query

Question

I am trying to built a free matrimonial site where the homepage holds a simple form with some textboxes & dropdowns and a button for search.

When clicked it takes the visitor to another page along with query strings of all fields.

I used the following stored procedure to show search result.

ALTER PROCEDURE dbo.homesearch
@gender varchar(6),
@age int,
@age2 int,
@religion varchar(50),
@status varchar(11),
@resCountry varchar(50),
@resCity varchar(50)
AS
SELECT * 
    FROM [users] 
    WHERE 
       ( ([age] > @age) OR ([age] < @age2) OR 
         ([gender] = @gender) OR ([religion] = @religion) OR 
         ([status] = @status) OR ([resCountry] = @resCountry) OR 
         ([resCity] = @resCity))

RETURN

The problem is I am stuck with OR & AND

if I use OR then it will show lots of irrelevant result.

if I use AND then what if any of the search query is empty.

Sorry I am new to this and my question might be too childish for you friends.

Another question if its the best & secure way to do so ? if not then how can I improve it.

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Jan 14 '14 at 01:15

score 1 · Answer 1 · edited May 23 '17 at 11:49

Use isNull on you parameters if you are using and...

SELECT * FROM [users] 
WHERE (([age] > isNull(@age, [age]) 
AND    ([age] < isNull(@age2, [age])
AND ([gender] = isNull(@gender, [gender]) 
AND ([religion] = isNull(@religion, [relegion]) 
AND ([status] = isNull(@status,[status])
AND ([resCountry] = isNull(@resCountry, [resCountry]) 
AND ([resCity] = isNull(@resCity, [resCity]))

Here is some reading on isNull and it's useful brother Coalesce. Coalesce can come in handy if you have null values in your data as null = null evaluates to false. See: Why does NULL = NULL evaluate to false in SQL server. As an example we will assume religion can have a value of null in the database.

SELECT * FROM [users] 
WHERE (isNull([religion],'N/A') = Coalesce(@religion, [relegion], 'N/A') 
... etc

I probably wouldn't use N/A in the real world, you could use an empty string, integer, anything you like as long as it is the same for both sides of the equality test.

On a side note, I would use <= and >= for the age test.

score 0 · Answer 2 · edited Jan 14 '14 at 01:34

It depends on what you want.

The difference between AND & OR in any programming language like TSQL is this :

When you use OR between conditions (Like: ([age] > @age) OR ([age] < @age2) OR ([gender] = @gender) OR ([religion] = @religion) ) It means if even there is one condition that is true, do this (SELECT * FROM users)

But when you use AND between conditions (Like: ([age] > @age) AND ([age] < @age2) AND ([gender] = @gender) AND ([religion] = @religion) ) Means if All of my conditions is true, do this (SELECT * FROM users)

So if you want that the result contain the users who have all of the conditions, use AND. But if you want the result contain the users who even have one of the conditions (or more), use OR.

score 0 · Accepted Answer · answered Jan 14 '14 at 03:30

so the basic idea is the parameter maybe is null or may not, so make the query like this

ALTER PROCEDURE dbo.homesearch
@gender varchar(6),
@age int,
@age2 int,
@religion varchar(50),
@status varchar(11),
@resCountry varchar(50),
@resCity varchar(50)

AS
SELECT * FROM [users] 
WHERE (@gender is null or gender = @gender)
AND ((@age is null or age > @age)
AND (@age2 is null or age <@age2))
AND (@religion is null or religion=@religon)
AND (@status is null or status =@status )
AND (@resCountry is null or resCountry =@resCountry )
AND (@resCity is null or resCity =@resCity )
RETURN

score 0 · Answer 4 · answered Jan 14 '14 at 06:16

0

Using Stored Procedure is one of the best practice and secure way to query data. You can also use "dynamic sql" to add more advance search feature but it is not a best practice to do so and it is prone to Sql injection attack.

answered Jan 14 '14 at 06:16

Vijaychandar

716
5
21

Advanced Search Query

4 Answers4