Shortcuts On And/or Logic With Different Variables

Hi,

I have a relatively common scenario. There is a stored procedure which takes two input parameters which are used as clauses in the query, e.g:


select * from myTable where (field1 = @param1 or field2 = @param2)


Now, the behaviour I'm ideally looking for is for the SP to implement different logic depending on what parameters have been supplied. If either one of the params has a value of zero then I want to run the query on the other param only, or if both params have values greater than zero, then I want to run the query using both parameters e.g.:


pseudocode:
If @param1 = 0 then
select * from myTable where field2 = @param2
else if @param2 = 0 then
select * from myTable where field1 = @param1
else if param2 <> 0 and param1 <> 0 then
select * from myTable where field1 = @param1 and field2 = @param2


Looks straightforward enough. In reality of course the actual query is much more complicated than that, and it's a mess to write it out three times just to do a bit of if/else logic.

There are two obvious solutions to this: either do the if/else in code and call different SPs, or make the query an SP on its own which is called by my original SP with different logic. For various reasons I'd rather not do either of these things.

This is such a common scenario that I'm wondering whether or not there's some easier, cleaner solution that I've missed, or don't know about. Is there?

Cheers,
Matt