SEO

#1 (**permalink**) 02-29-2008, 06:39 PM

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

#2 (**permalink**) 02-29-2008, 06:39 PM

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.

#3 (**permalink**) 02-29-2008, 06:40 PM

Sweet! Thanks man.

"figital" <mharen@gmail.com> wrote in message
news:1141927222.299970.274010@i39g2000cwa.googlegr oups.com...
> 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.
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Help optmizing a stored proc