CODEWITHSUNDEEP
sqlstored-procedures
blue piranha
blue piranha

Reputation: 3874

conditional stored procedure with/without passing parameter

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

Upvotes: 2

Views: 2584

Answers (3)

canon
canon

Reputation: 41675

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

Upvotes: 4

Robert
Robert

Reputation: 25753

Shorter way to do that:

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

You can't chack if value is null using =. For your example you have to replace condition @studentId = null to is null syntax.

Try to change your code as below:

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

Upvotes: 2

logixologist
logixologist

Reputation: 3834

Change the = to an is

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

Upvotes: 0

Related Questions