help with parameter in WHERE

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

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.Order_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.Try:

where Product_Sales.Order_Amount =
CASE
when @.p1 is null then Product_Sales.Order_Amount
else @.p
END

--

Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"knowzero" <isacp@.bhphoto.comwrote in message
news:1174504953.877881.232990@.b75g2000hsg.googlegr oups.com...

Quote:

Originally Posted by

my query:
select Product_List.*, Product_Sales.*
from Product_List
left outer join Product_Sales on Product_List.ID = Product_Sales.ID
>
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.Order_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.
>

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

Quote:

Originally Posted by

>my query:
>select Product_List.*, Product_Sales.*
>from Product_List
>left outer join Product_Sales on Product_List.ID = Product_Sales.ID
>
>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.Order_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.Order_Amount >= COALESCE(@.p1,
Product_Sales.Order_Amount)

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

Quote:

Originally Posted by

my query:
select Product_List.*, Product_Sales.*
from Product_List
left outer join Product_Sales on Product_List.ID = Product_Sales.ID
>
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.Order_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.Order_Amount >= @.p1