CODEWITHSUNDEEP
asp.nett-sql
Dmitry Makovetskiyd
Dmitry Makovetskiyd

Reputation: 7053

T-SQL search results OR/AND condition

I am building a system where the user can search users in a forum by gender, age language, users interest.

I have got this stored procedure:

ALTER PROCEDURE dbo.FindFriends
    (
    @language int,
    @country nvarchar(50),
    @sex int,
    @ageFrom int,
    @ageTo int,
    @category nvarchar(50)
    )
AS
    SELECT u.*, cat.*
    FROM  [User] AS u
    INNER JOIN categories AS cat ON u.user_id=cat.user_id 
    WHERE (age_presenentation BETWEEN @ageFrom AND @ageTo) AND lingo=@language AND sex=@sex AND cat.category_name=@category

This statement is what I want. But sometimes, the user may not enter some of the fields. For example, the user might search users by category alone.. This will result in no users appearing, since I am using "And" operator. Is there a way to combine the logic, so that if any of the sql variables (e.g. @category, @angeTo) are given as empty, the statement will not include them?

Upvotes: 0

Views: 103

Answers (2)

NickV
NickV

Reputation: 649

I have run into this many times.

Try:

WHERE (age_presenentation BETWEEN @ageFrom AND @ageTo)
AND ((@language IS NULL) OR (lingo=@language))
AND ((@sex IS NULL) OR (sex=@sex))
AND ((@category IS NULL) OR (cat.category_name=@category))

You can apply the same logic to the @ageFrom as well:
((@ageFrom IS NULL) OR (age_presenentation BETWEEN @ageFrom AND @ageTo))

But that may be too many records.

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 839174

It depends on what you mean by "empty". If you mean NULL then try this:

WHERE age_presenentation BETWEEN @ageFrom AND @ageTo
AND (lingo=@language OR @language IS NULL)
AND (sex=@sex OR @sex IS NULL) 
AND (cat.category_name=@category OR @category IS NULL)

Upvotes: 2

Related Questions