dynamic where clause

Hi

I need some advice on which direction to take!

Consider this statement:

SELECT business_name FROM myTable WHERE town = @.town AND county = @.county

My problem is that i will not always have the @.county variable available. Is there a way to use an IF or a CASE inside the SQL statement (i know i can create two seperate sql statments but dont want to do it this way)? If it makes it easier, when the @.county variable is not available, it has a value of 0.

thanks again

Ps, i also know how to do it using dynamic sql using the EXEC() command, but i'd prefer to steer clear of this method also.SELECT business_name
FROM myTable
WHERE (town = @.town OR @.town IS NULL)
AND (county = @.county OR @.county = 0)
?|||Hi pootle

If the @.county has a value of 0 then i don't want to make it part of the WHERE clause, ie i dont want it to search the county column - in your example it would search all counties which are equal to 0?

This is what i'm trying to acheive, but only use one SELECT statment:

IF @.county <> '0'
BEGIN
SELECT business_name FROM myTable WHERE town = @.town AND county = @.county
END
ELSE
BEGIN
SELECT business_name FROM myTable WHERE town = @.town
END|||Hi Mattock

Did you try the code? I know the answer is no :) It does just what you ask for.|||lol, i'm real sorry, got the monday morning blues! Thick mode was well and truly stuck to 'ON'.

cheers again|||No probs :)