sql - conditional stored procedure with/without passing parameter

Question

I created a stored procedure which when passed nothing as parameter should return the entire table. But if the studentId is passed, then return her details. Something like this

create procedure usp_GetStudents @studentId int = null
as
  if (@studentId = null)
    select * from Student
  else
    select * from Student where studentId = @studentId

Output

exec usp_GetStudents -- No records returned though there are records in the table


exec usp_GetStudents @studentId = null  -- No records returned


exec usp_GetStudents @studentId = 256  -- 1 entry returned

Just curious to know if anything is wrong in the syntax/logic for returning all the entries of the table?

Thank you

Answer 1

You're trying to test for null using =, a comparison operator. If you're using ANSI nulls, any comparison against null is false.

Where @studentId is any value (or null) the following expressions are all false:

@studentId = null  -- false
@studentId > null  -- false
@studentId >= null  -- false
@studentId < null  -- false
@studentId <= null  -- false
@studentId <> null -- false

So, in order to test for null you must use a special predicate, is null, i.e.:

@studentId is null