SQL server conditional select statement

Question

Ok, this might be an easy one, but I just can't get it.

I am creating a page which will query a table with many columns and most items are not unique. I need to be able to get a list of records that match as many of the (up to 4) search criteria as possible.

Example:

I am user searching for the following items, I enter at least one and up to 4 of the items below in a text box: Name, age, gender, weight (user may or may not fill in all of them).

If he just enters "F" for gender, then he will get a list of thousands of females with their name, age, gender and weight.

However if he enters "F" for gender and "300" for weight, he will get a much smaller list of returned records.

I need to be able to create a sql statement that can perform that search with that functionality.

advTHANKSance

Answer 1

I've used similar to the one below to do what you are trying:

DECLARE @Gender varchar(1)
DECLARE @Age int
DECLARE @Weight int
DECLARE @Name varchar(64)

 SELECT * FROM MyTable
    WHERE
    (@Gender is null OR Gender = @gender)
    AND (@weight is null OR Weight = @weight)
    AND (@Age is null OR  age = @Age)
    and (@Name is null OR Name = @Name)

if you were to create a stored procedure (which i would recommend) it would look like this:

CREATE PROCEDURE SelectRecords
    @Gender varchar(1),
    @Age int,
    @Weight int,
    @Name varchar(64)
AS
     SELECT * FROM MyTable
        WHERE
        (@Gender is null OR Gender = @gender)
        AND (@weight is null OR Weight = @weight)
        AND (@Age is null OR  age = @Age)
        and (@Name is null OR Name = @Name)

What this stored procedure is doing is checking to see if you passed a value in for the specific parameter. If you DID NOT then it will be null and the condition will be true. if you DID then it will not be null and the second condition must evaluate to true for the record to be returned.

Answer 2

Seeing as you are using ASP.NET, you could take a look at LINQ-to-SQL which solves this in a very elegant way:

var query = db.T_Persons;
if (txtGender.Text != string.Empty)
    query = query.Where(x => x.Gender == txtGender.Text);
if (txtWeigth.Text != string.Empty)
    query = query.Where(x => x.Weight == int.Parse(txtWeight.Text));
...

Of course, you'll need to be using .NET 3.5 or newer.

Answer 3

I did stuff like this by combining a null check with the parameter. If it was null then everything got included, if not then the other part actually mattered

CREATE myFunnyProc ( @p1 nvarchar(10), @p2 nvarchar(10) ) AS BEGIN
    SELECT * FROM dbo.myReallyLongTable table
    WHERE
             (@p1 is null or table.name LIKE @p1) 
        AND  (@p2 is null or table.age  = @p2)

END

Answer 4

I've often seen this done with the following SQL statement (where @gender, @weight, @age, and @name are filled in with data from the user, and gender, weight, age, and name are table fields):

SELECT * FROM MyTable
WHERE
    gender = COALESCE(@gender, gender)
    AND weight = COALESCE(@weight, weight)
    AND age = COALESCE(@age, age)
    and name= COALESCE(@name, name)

(Edit: I just wanted to add a short explanation of why this works for anyone not familiar with coalesce. The coalesce function takes the first not-null value of the 2 passed to it. So if there is something in the @ parameter, which means the user entered data for that field, it will check if the field equals that user-entered value; if the user didn't enter anything and the @ parameter is null, it will test against the second value, which is the field itself - and as the field is always equal to itself, this will return all records - it won't filter based on this field at all.)

Answer 5

The answer is something most people try to stay away from it is dynamic sql.

I would suggest creating a stored procedure you can call for this, but here is the code. You need to put the name of your table in place of tablename.

Declare 
    @gender varchar(1),
    @weight int,
    @age int,
    @name varchar(100), 
    @sql varchar(200),
    @variableCount int

set @variableCount = 0 

set @sql = 'select * from tablename'

if(@gender is not null)
Begin
@sql += ' where gender = @gender'
@vaiableCount = @VariableCount+1
End

if(@weight is not null)
Begin
if(@variableCount = 0)
Begin
@sql += ' Where ' 
End 
else 
@sql += ' And '
@sql += 'weight = @weight'
End

if(@age is not null)
Begin
if(@VariableCount = 0)
Begin
@sql += ' where '
End
else 
@sql += ' And '
@sql += 'age = @age'
End

if(@name is not null)
Begin
if(@VariableCount = 0)
Begin
@sql += ' where '
End
else 
@sql += ' And '
@sql += 'name = @name' 
End

execute @sql 

Answer 6

@Abe - Your solution will work IF Age and Weight are not between Name and Gender in the SELECT statement. I know cuz I tried it :) on SQL Server 2008. I made a table with many records with NULLs scattered throughout. I also made a proc, and running it as you wrote it would not return rows when age or weight were entered as parameters. I moved the int based params in the SELECT statement to the top or bottom of list, both worked. But nesting the int params between the string params in the select statement and then specifying the int params made it fail...

So why did the original not work?? :) Anyone?

BTW, the COALESCE will not work at all (yes, I tested it and reordered the SELECT statement in the proc). It LOOKED like it should work...