Kevin,

I thought that using COALESCE sounded like a cool idea, so I messed with one of my Stored Procs to see if I could use this methodology. My SELECT was more complex in that I had JOINs to two other tables and it didn't work for me. Will this only work with simple SELECTs?

~~Bonnie



>Hey, Kevin,
>
>First, I agree with Bonnie. Since this is an entirely different question, it really should be a different thread.
>
>Bonnie gave you a solution that works, and John is right-on about avoiding a solution that's prone to SQL injection.
>
>There's another solution you might want to look at. I don't know if this will satisfy your needs, but...when you have several possible parameters, but only a few may be used at any one time, you can use the COALESCE function in T-SQL. For example...
>
>

>PARAMETERS @cFirstName Char(20), @cLastName Char(20), @cAddress char(30), @cCity char(30)
>
>SELECT * FROM Customers WHERE FirstName = COALESCE(@cFirstName,Firstname) AND
>				LastName = COALESCE(@cLastName,Lastname) AND
>				Address = COALESCE(@cAddress,Address) AND
>				cITY = COALESCE(@cCity,City)
>
>

>
>COALESCE returns the first non-null expression. So if you want to do a lookup based on City and LastName, just pass
>NULL as a parameter for @cFirstName and @cAddress - and the query will (essentially) be FirstName = FirstName
>and Address = Address, in addition to City = @cCity and LastName = @cLastName
>
>
>Hope that helps...
>Kevin