NULL check in Sql

Question

Please suggest whats the difference between:

1) WHERE student.name = ISNULL(@name, student.name)

And

2) WHERE (student.name = @name OR @name IS NULL)

Actually I had a issue bug assigned against my name where some of the records where skipped when I used the first method. But got corrected when I replaced it with eg 2.

Answer 1

The second parameter for ISNULL() is a replacement value, meaning the value that will be substituted if the first parameter is null. Thus, if both @name and student.name were null, you'd essentially be writing:

student.name = NULL

Which equates to false (as null in SQL is considered unknown, and never equals anything).

In the second, you're using the IS operator to test for a null value, which will return true if @name is null.