have paramaters only used "if defined" 1

[akalinowski]

is there a way to have sqlserver reporting services only consider a pareter or a section of the query if a parameter is defined.
for example lets say i have a query that looks like this:

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_COST, PRODUCT_PRICE, PRODUCT_STOCK
WHERE PRODUCT_COST = @COST
AND PRODUCT_STOCK = @STOCK

if i only enter a value for cost, i want it to show me all product no matter what the stock is, and if i tell i ti want stock of X i want to to show me all product ignoring the cost
make sense?

i do a little coldfusion and in there you can use isdefined
and use that to make my logic work on the query where it only uses parts of the querey where i defined it in the variable.

akalinowski

http://www.olivelabs.com

 

[RiverGuy]

Queries like these are done all of the time. Consider the example below. If @COST is not null, then it will filter on the cost, otherwise, bring all costs back. If @STOCK is not null, it will filter on the stock, otherwise, bring back all stocks. You could filter on one or the other, both, or neither with this logic.

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_COST, PRODUCT_PRICE, PRODUCT_STOCK
WHERE (PRODUCT_COST = @COST OR @COST IS NULL)
AND (PRODUCT_STOCK = @STOCK OR @STOCK IS NULL)

 

[akalinowski]

sweet, that worked
thanks.

akalinowski

http://www.olivelabs.com