You are not too far off.

WHERE

(Identifier = @identifier OR @identifier IS NULL)

AND ( Date = @date OR @date IS NULL)

AND ( Forename LIKE '%'+@for+'%' )

AND ( Surname LIKE '%'+@sur+'%')

This assumes

- @identifier and @date are non-string datatypes and cannot contain blanks.

- Forename & Surname are not nullable and can contain blanks. If they are nullable, you'll need to add the null test logic depending on the different meaning you'd have in place for null vs blank.

The biggest problem you'll run into with this type of WHERE clause is that it nearly always leads to a table scan. Although not ideal, table scans are not the end of the world. However, if the table you're filtering is also being joined to a number of other tables, your query may end up taking until the end of the world to return because the optimizer doesn't understand what you want. Even option (recompile) statements won't help because the WHERE clause is not sargable.

In that scenario, I like to use a filter first approach. It will look something like this:

SELECT pk

INTO #primarytable

FROM primarytable

WHERE(Identifier = @identifier OR @identifier IS NULL)

AND ( Date = @date OR @date IS NULL)

AND( Forename LIKE '%'+@for+'%' )

AND ( Surname LIKE '%'+@sur+'%')

SELECT *

FROM #primarytable AS keyfilter

INNER JOIN primarytable AS pt

ON pt.pk = keyfilter.pk

INNER JOIN [all other tables]...

WHERE [clauses not touching the primary table]...

OPTION (RECOMPILE)

The benefit is that your non-sargable query and table scan are targeted to a single table and the optimizer doesn't have to figure out a plan that works with all of the other tables you're joining. Using the Recompile in the second query allows the optimizer to create a plan appropriate for the number of rows that matched the primary tables filters.

The join on PKs between the temp table and the primary table is a very high performance index seek and is almost guaranteed to be one of the first operations, which will reduce the number of rows that the rest of the plan operations have to touch. I've used this with great success when writing theses type of reporting queries from the TFS database (millions of rows, 10-20 joins, potentially dozens of filters).

There are tradeoffs, such as forced tempdb usage and touching a single table multiple times, but I've found these to be acceptable over a slow running query that will likely spill to tempdb anyway.

On a separate note, a concise query doesn't necessarily mean high performance. Many times, the quick way to write a SQL script generates slower code. Aim for performance and maintainability. I feel abbreviations (parameter/table/column name, etc) hurt maintainability. They generally make maintenance harder because the next developer has to interpret your abbreviation. I would use @forename and @surname in your example.