>>I would write this condition as
>>
>>select * from myTable where (@Param1 IS NULL or Col_IntType = @Param1)
>>
>>In the first case, the records where Col_IntType are NULL are not returned. In the second case they are returned. If you don't have records with NULL values in your table, then both where expressions will return the exactly same results.
>
>Are you saying that the Where clause I posted ("where Col_IntType = COALESCE(@param1, Col_IntType)" will not return records with NULL value? Why not? If @param1 is NULL than the expression will evaluate to Col_IntType = Col_IntType; and wouldn't it be always True regardless of the value in the field?

Yes, this is exactly what I'm saying. Run this simple test to see for yourself:

declare @t table (ID int identity(1,1) primary key, intField int NULL)

insert into @t select 1 union select null union select 2
declare @param int
set @param = null

select * from @t where IntField  = coalesce(@param,intField)
select * from @t where (@param IS NULL or intField = @param )

The reason behind is simple.

You can not compare with NULL with =, you need to use IS operator when comparing with NULL.

So, myValue == NULL is incorrect and it would not give you a result even if myValue is NULL, while myValue IS NULL is the correct test and will give you the result.