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