• Always good to see a firm grasp of logic being used in computing!

    If I may suggest a variation to this technique I have seen is to use ISNULL or COALESCE around the parameter

    ALTER PROCEDURE [spSearchPeople]

    @firstname nvarchar(50) = null,

    @lastname nvarchar(50) = null

    AS

    BEGIN

    SELECT ID, FirstName, LastName FROM People

    WHERE

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

    END

    If @firstname is NULL then the % + @firstname + % becomes NULL, so the table self matches

    This should mean less confusion over indices and better query plan caching as the optimiser can determine that the column we are interested in is FirstName

    The major drawback is it is less intuitive and probably T-SQL specific