How can I use optional parameters in a T-SQL stored procedure?

by

I am creating a stored procedure to do a search through a table. I have many different search fields, all of which are optional. Is there a way to create a stored procedure that will handle this? Let’s say I have a table with four fields: ID, FirstName, LastName and Title. I could do something like this:

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    BEGIN
        SELECT ID, FirstName, LastName, Title
        FROM tblUsers
        WHERE
            FirstName = ISNULL(@FirstName, FirstName) AND
            LastName = ISNULL(@LastName, LastName) AND
            Title = ISNULL(@Title, Title)
    END

This sort of works. However it ignores records where FirstName, LastName or Title are NULL. If Title is not specified in the search parameters I want to include records where Title is NULL – same for FirstName and LastName. I know I could probably do this with dynamic SQL but I would like to avoid that.

6 Answers
6

Leave a Comment