IF ELSE with WHERE, AND, OR

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jcochran@rethinkllc.com - 28 Jul 2005 22:53 GMT

What would be the correct way of writing a sql select state with where
clause while also using IF ELSE. I am using T-SQL and I cannot get it
to work. I probably have the syntax wrong.

I want to be able to have different where/and/or clauses in the sql
dependant on what value was passed into the @SearchTerm parameter in
this stored procedure.

Can I use CASE statements in the WHERE section? Or is that strickly for
SELECT statements?

Code as follows:

================================================================

CREATE PROCEDURE spTicketReport
(
@SearchTerm varchar(100) = NULL
)
AS
BEGIN
SELECT TOP 100 PERCENT Tickets.TicketID, Tickets.TicketNumber AS
TicketNumber, Haulers.Name AS Hauler, Leases.LeaseID AS LeaseID,
Leases.LeaseName AS Lease, Shippers.Name AS
Shipper, Tickets.FeeTox, Tickets.FeeWashout, Tickets.FeeDisposal,
Tickets.Yards, Tickets.Barrels,
Tickets.FluidSolidRatio, DATEPART(yyyy, Tickets.DateAdded) AS [Year]
FROM Tickets INNER JOIN
Leases ON Tickets.LeaseID = Leases.LeaseID INNER
JOIN
Haulers ON Tickets.HaulerID = Haulers.HaulerID
INNER JOIN
Shippers ON Tickets.ShipperID =
Shippers.ShipperID
WHERE TicketNumber LIKE '%' + @SearchTerm + '%' OR Haulers.Name LIKE
'%' + @SearchTerm + '%' OR Shippers.Name LIKE '%' + @SearchTerm + '%'
OR Leases.LeaseName LIKE '%' + @SearchTerm + '%'
ORDER BY TicketNumber, Shipper, Hauler

================================================================

Thanks in advance!

Jason Cochran
Rethink Technologies, L.L.C.
www.rethinkllc.com

Reply to this Message

Erland Sommarskog - 28 Jul 2005 23:42 GMT

> What would be the correct way of writing a sql select state with where
> clause while also using IF ELSE. I am using T-SQL and I cannot get it
[quoted text clipped - 6 lines]
> Can I use CASE statements in the WHERE section? Or is that strickly for
> SELECT statements?

You cannot use CASE statements, because there are none in T-SQL. But
you can use CASE expressions in a WHERE clause:

WHERE CASE WHEN @SearchTerm LIKE <a ticket number>
THEN TicketNumber LIKE '%' + @SearchTerm + '%'
WHEN @SearchTerm LIKE <a haluers name>
THEN Haulers.Name
ELSE Leases.LeaseName
END LIKE '%' + @SearchTerm + '%'

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Reply to this Message

Zero.NULL - 29 Jul 2005 14:05 GMT

This is a good piece of information, however it can be achieved by the
other way as well...
By using parantheses and boolean operators (AND, OR, NOT) properly.

Something like this:

WHERE     ( @SearchTerm LIKE <a ticket number>      AND    TicketNumber LIKE '%'
+ @SearchTerm + '%' )
    OR
    ( @SearchTerm LIKE <a haluers name>     AND    Haulers.Name LIKE '%' +
@SearchTerm + '%' )
    OR
    ( Leases.LeaseName LIKE '%' + @SearchTerm + '%' )

Reply to this Message

jsfromynr - 29 Jul 2005 14:44 GMT

Hi Erland,
Very informative answer , but from performance point of view we should
not be using Like

Most DBMSs will use an index for a LIKE pattern if it starts with a
real character but will avoid an index for a LIKE pattern that starts
with a wildcard (either % or _). The only DBMSs that never use indexes
for LIKE are Pick and mSQL (on TEXT fields). For example, if the search
condition is:

... WHERE column1 LIKE 'C_F%'

DBMSs will resolve it by finding all index keys that start with C and
then filtering those that contain F in the third position. In other
words, you don't need to transform this search condition:
Here '%' is being used at the beginning so I think using charindex will
do fine job (Please correct it if wrong)

Where charindex
( @SearchTerm,
(
CASE
WHEN charindex(@SearchTerm, a ticket number )>0 THEN TicketNumber

WHEN charindex(@SearchTerm,a haluers name) > 0 THEN Haulers.Name

ELSE Leases.LeaseName
END
)
)>0

With warm regards
Jatinder

Reply to this Message

jcochran@rethinkllc.com - 29 Jul 2005 16:19 GMT

I appreciate everyones help on this.

What if I wanted to add another parameter named @AccountID. AccountID
is used to track who created the ticket. @AccountID would be set to
NULL just like @SearchTerm is. I wanted to be able to check to see if
either was passed in. Sometimes both will be; other times it will be
either/or.

============ PSEUDO CODE ===================

WHERE TicketID != 0

IF NOT @SearchTerm IS NULL THEN
AND (TicketNumber LIKE '%' + @SearchTerm + '%' OR Haulers.Name
LIKE '%' + @SearchTerm + '%' OR Shippers.Name LIKE '%' + @SearchTerm +
'%' OR Leases.LeaseName LIKE '%' + @SearchTerm + '%' )
END IF
IF NOT @AccountID IS NULL THEN
AND AccountID = @AccountID
END IF

ORDER BY TicketNumber, Shipper, Hauler

============ END PSEUDO CODE ===================

Reply to this Message

Erland Sommarskog - 29 Jul 2005 16:29 GMT

> I appreciate everyones help on this.
>
[quoted text clipped - 7 lines]
> AND AccountID = @AccountID
> END IF

AND (AccountID = @AccountID OR @AccountID IS NULL)

However, beware that if you want any index on AccuontID to be use, you
better split this up and have two different SELECT statements.

For a much longer discussion on a problem which you have not really
reached, but seem to be on your way to, I have an article on my web
site that you can save for a rainy day:
http://www.sommarskog.se/dyn-search.html.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Reply to this Message

jcochran@rethinkllc.com - 29 Jul 2005 18:01 GMT

Looking at that article you mentioned; under if statements, the code
below is mentioned. It just seems like a very nasty way of doing
things. I could do it this way BUT I just think there should be a much
cleaner way of doing it. If I had to change/remove/add a column in the
select statement, I would have 3 other places to do it in. The WHERE
statement should be the only thing that is different. I shouldnt have
to have the same select statement 3 times.

IF @orderid IS NOT NULL
BEGIN
SELECT ...
WHERE o.OrderID = @orderid
AND od.OrderID = @orderid
AND (od.UnitPrice >= @minprice OR @minprice IS NULL)
AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
AND (od.ProductID = @prodid OR @prodid IS NULL)
AND (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER BY o.OrderID
END
ELSE IF @custid IS NOT NULL
BEGIN
SELECT ...
WHERE (o.OrderDate >= @fromdate OR @fromdate IS NULL)
AND (o.OrderDate <= @todate OR @todate IS NULL)
AND (od.UnitPrice >= @minprice OR @minprice IS NULL)
AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
AND c.CustomerID = @custid
AND o.CustomerID = @custid
AND (od.ProductID = @prodid OR @prodid IS NULL)
AND (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER BY o.OrderID
END
ELSE
BEGIN
SELECT ...
WHERE (o.OrderDate >= @fromdate OR @fromdate IS NULL)
AND (o.OrderDate <= @todate OR @todate IS NULL)
AND (od.UnitPrice >= @minprice OR @minprice IS NULL)
AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
AND (c.CompanyName LIKE @custname + '%' OR @custname IS NULL)
AND (c.City = @city OR @city IS NULL)
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @country OR @country IS NULL)
AND (od.ProductID = @prodid OR @prodid IS NULL)
AND (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER BY o.OrderID
END

Reply to this Message

Erland Sommarskog - 29 Jul 2005 22:31 GMT

> Looking at that article you mentioned; under if statements, the code
> below is mentioned. It just seems like a very nasty way of doing
> things.

This is indeed not a method that scales well in terms of maintenance
when you have many different conditions, and I also note this in the
article.

> I could do it this way BUT I just think there should be a much
> cleaner way of doing it. If I had to change/remove/add a column in the
> select statement, I would have 3 other places to do it in. The WHERE
> statement should be the only thing that is different. I shouldnt have
> to have the same select statement 3 times.

Well, it depends with you mean with cleaner. You can do all in one
single static SQL statement, and from the perspective of maintenance
and functionality you would be fine. However, SQL programming is also
a lot about performance. If your table has 100 million rows, you don't
want a table scan to happen on an interactive query.

For this reason, one sometimes has to duplicate code in a way that
conflicts with the best practices you've learnt when working with
traditional languages.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Reply to this Message