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

Just one block:

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

Note this last line. If @Filter is NULL, the entire block is ALWAYS
true so "TO1.Retired <> @Filter" doesn't matter. If @filter is not
null, "TO1.Retired <> @Filter" is the part that matters.

Sweet! Thanks man.

"figital" <mharen (AT) gmail (DOT) com> wrote