Posted by CK on 03/09/06 19:45

I have this

CREATE PROCEDURE dbo.cmsGetTaskOrdersAndFunding2
(
@FundingDate SMALLDATETIME,
@BillingContractID INT, -- null for all contracts
@Filter BIT = NULL

)
AS
-- get list of taskorders with their respective fundingtotals as of
specified date
IF @Filter IS NULL
BEGIN
SELECT TO1.TaskOrderID
FROM TaskOrder TO1
LEFT OUTER JOIN
WHERE (@BillingContractID IS NULL OR TO1.BillingContractID =
@BillingContractID)
END
ELSE
BEGIN
SELECT TO1.TaskOrderID,
FROM TaskOrder TO1
WHERE (@BillingContractID IS NULL OR TO1.BillingContractID =
@BillingContractID) AND TO1.Retired <> @Filter
END

RETURN
GO
------------------
Is there a less redundant way to write this? basically @Filter is an
optional parameter, if it isn't present, I want to return all records and if
it is present all records where Retired <> @Filter. Any ideas? Can I wrap
the WHERE clause in an if statement? Or is there a better way?

TIA,
Chris

[Back to original message]