help with parameter in WHERE

[knowzero]

my query:
select Product_List.*, Product_Sales.*
from Product_List
left outer join Product_Sales on Product_List.ID = Product_Sales.I D

this works fine. its simple. I have this in a report and I would like
to add a parameter @p1 to add to the query

where Product_Sales.O rder_Amount >= @p1

my problem is that if @p1 is null I want to retrieve all recorders in
Product_List. any ideas.

Do you need more clarification.

[Jack Vamvas]

Try:

where Product_Sales.O rder_Amount =
CASE
when @p1 is null then Product_Sales.O rder_Amount
else @p
END
--

Jack Vamvas
_______________ _______________ _____
Advertise your IT vacancies for free at - http://www.ITjobfeed.com

"knowzero" <is***@bhphoto. comwrote in message
news:11******** **************@ b75g2000hsg.goo glegroups.com.. .

my query:
select Product_List.*, Product_Sales.*
from Product_List
left outer join Product_Sales on Product_List.ID = Product_Sales.I D

this works fine. its simple. I have this in a report and I would like
to add a parameter @p1 to add to the query

where Product_Sales.O rder_Amount >= @p1

my problem is that if @p1 is null I want to retrieve all recorders in
Product_List. any ideas.

Do you need more clarification.

[Hugo Kornelis]

On 21 Mar 2007 12:22:38 -0700, knowzero wrote:

>my query:
select Product_List.*, Product_Sales.*
from Product_List
left outer join Product_Sales on Product_List.ID = Product_Sales.I D

this works fine. its simple. I have this in a report and I would like
to add a parameter @p1 to add to the query

where Product_Sales.O rder_Amount >= @p1

my problem is that if @p1 is null I want to retrieve all recorders in
Product_List . any ideas.

Hi knowzero,

WHERE Product_Sales.O rder_Amount >= COALESCE(@p1,
Product_Sales.O rder_Amount)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Ed Murphy]

knowzero wrote:

my query:
select Product_List.*, Product_Sales.*
from Product_List
left outer join Product_Sales on Product_List.ID = Product_Sales.I D

this works fine. its simple. I have this in a report and I would like
to add a parameter @p1 to add to the query

where Product_Sales.O rder_Amount >= @p1

my problem is that if @p1 is null I want to retrieve all recorders in
Product_List. any ideas.

where @p1 is null or Product_Sales.O rder_Amount >= @p1