Posted by Erland Sommarskog on 10/01/78 11:23

(jcochran@rethinkllc.com) writes:
> 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?

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 + '%'



--
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

[Back to original message]