Is there a way to make a null check on a variable in a WHERE clause only occur once?

I have a query on a large table that looks like this:

declare @myIdParam int = 1

select * 
from myTable
where (@myIdParam is null or myTable.Id = @myIdParam)

There are several similar conditionals like this in the where clause, and there are also a lot of joins, but this is a summary.

Effectively, if @myIdParam is null, we do not want to restrict the results using this parameter.

I am not a DB pro, but from my tests it seems like this NULL check is done for every record and not optimized out in any way.

If I remove the null check and assume the parameter is not null, the query returns instantly. Otherwise, it takes up to ten seconds.

Is there a way to optimize this so the check is done only once at runtime?

One way is to use dynamic SQL, using a null check to optionally add that part of the where clause.

declare @myIdParam int = 1
declare @vc_dynamicsql varchar(max)

set @vc_dynamicsql = 'select * from myTable where 1=1'

if @myIdParam is not null
    set @vc_dynamicsql = @vc_dynamicsql + ' and  myTable.Id = @myIdParam'

EXECUTE sp_executesql @vc_dynamicsql

StackExchange Database Administrators Q#148016, answer score: 8