Very nice and pragmatic article.

I also like the solution proposed by Toby Harman, though I'm not able to determine the impact on performance.

I propose another solution, the one that I use most :hehe: :

ALTER PROCEDURE [spSearchPeople]

@firstname nvarchar(50) = null,

@lastname nvarchar(50) = null

AS

BEGIN

SELECT ID, FirstName, LastName FROM People

WHERE

FirstName LIKE '%' + ISNULL(@firstname, FirstName) + '%'

END

obviously it also works perfectly with equal where conditions 😛

ALTER PROCEDURE [spSearchPeople]

@firstname nvarchar(50) = null,

@lastname nvarchar(50) = null

AS

BEGIN

SELECT ID, FirstName, LastName FROM People

WHERE

ISNULL(@firstname, FirstName) = FirstName

END

Cheers