help with parameter in WHERE

#1

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.

#2

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 (AT) bhphoto (DOT) com> wrote

Quote:

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.

#3

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

Quote:

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

#4

knowzero wrote:

Quote:

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

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

help with parameter in WHERE

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

help with parameter in WHERE - 03-21-2007 , 01:22 PM

Re: help with parameter in WHERE - 03-21-2007 , 01:44 PM

Re: help with parameter in WHERE - 03-21-2007 , 02:03 PM

Re: help with parameter in WHERE - 03-21-2007 , 04:56 PM