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