Checking for null value in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Checking for null value in stored procedure

Author

Topic

kannis1
Starting Member

4 Posts

Posted - 2006-07-03 : 00:33:22

I'm having a problem with the following stored procedure. All I want to do is check if specialtyId is null. If it is, run the first query, if not run the second. The first one won't run if I pass it a null value. What am I doing wrong?

CREATE Proc as_getCounselorBy_SpecialtyId
@SpecialtyId int = null
AS
Begin
if @SpecialtyId = null

Select *
from awCounselor
where counselorId In
(Select counselorId from awCoachSchedule)
and activityStatus =1
order by signUpdate DESC
else
Select *
From awCounselor a (noLock)
Join awCounselorSpecialty b (noLock)
On a.counselorId = b.counselorId
Where b.specialtyId = @SpecialtyId
And a.activityStatus = 1
Order by signUpdate Desc

End
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-03 : 00:46:06

You have to use IS NULL