CODEWITHSUNDEEP
sql
Sheenie
Sheenie

Reputation: 153

How to do a search function with two like parameters in sql

I am trying to do a search function with sql and asp.net. I have created a Gridview that uses the following sqldatasource sql statment to retrieve the data.

SELECT customer.fisrtname,
       customer.lastname,
       customer.initials,
       customer.title,
       address.street,
       area.area,
       suburb.suburbname
FROM   address
       INNER JOIN area
               ON address.areaid = area.areaid
       INNER JOIN areasuburb
               ON area.areaid = areasuburb.areaid
       INNER JOIN customeraddress
               ON address.addressid = customeraddress.addressid
       INNER JOIN customer
               ON customeraddress.customerid = customer.customerid
       INNER JOIN suburb
               ON areasuburb.suburbid = suburb.suburbid
WHERE  ( customer.lastname LIKE '%' + @Lastname + '%' )
        OR ( address.street LIKE '%' + @Street + '%' )

The problem I am having is that if the user does not input any value into the last name field then the statement does not return any data even though there is valid data.

Please any help with this would be greatly appreciated.

Upvotes: 2

Views: 110

Answers (2)

Jim
Jim

Reputation: 6881

Try using a CASE statement to replace @LastName with an underscore (underscore is a single character wildcard) if it is null.

So it would be like this...

WHERE  
  ( customer.lastname LIKE '%' + CASE WHEN (@LastName IS NULL) THEN '_' ELSE @Lastname END + '%' )
OR
  ( address.street LIKE '%' + @Street + '%' )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Here is one way to do optional parameters:

WHERE  (@Lastname is NULL or customer.lastname LIKE '%' + @Lastname + '%' ) or
       (@Street is NULL or address.street LIKE '%' + @Street + '%' )

It just ignores the condition when the parameter is NULL (or you might use = '', depending on how you represent the fact that no parameter was input).

Upvotes: 1

Related Questions