I found the problem. It has to do with NULLs in the columns you're searching. Here's a very simple example:

CREATE PROCEDURE GetCustomer
  @lastname  varchar(40) = NULL,
  @firstname varchar(40) = NULL,
  @middle    char(1)     = NULL
AS
   SELECT * FROM Customer
    WHERE lastname  = COALESCE(@lastname, lastname)
      AND firstname = COALESCE(@firstname, firstname)
      AND middle    = COALESCE(@middle, middle)

Plenty of times when a middle initial is not entered and will be NULL. This won't work in that situation. If you say

exec GetCustomer @lastname = 'Berent'

And I have a NULL middle initial, this will return no rows. So, I guess it's not an ideal solution without further tweaking for NULLs

~~Bonnie



>Hey, Bonnie,
>
>I just did a test with a JOIN, and had a COALESCE option into both tables, and it worked the way I would have expected. I'm not aware of any specific limitation or problem. I did a few searches on different sql forums and didn't come across anything, but maybe you've run into a limitation that I haven't.
>
>So maybe you're query is doing something more involved - can you post the code?
>
>Thanks,
>Kevin