Problem with Stored Proc - SQL Server Forums

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)

Problem with Stored Proc

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2003-11-28 : 14:15:26

Hello...

I am having a problem getting the correct results returned from a stored procdure...and I think the problem has to do with NULL in one of the columns.

Here is a snippet from the StoredProc

CREATE PROCEDURE GetUsersInRadius
( @LongWest decimal (18,7),
@LongEast decimal(18,7),
@LatSouth as decimal(18,7),
@LatNorth as decimal (18,7),
@MedSpec [varchar](50) = Null,
@Degree [varchar](20)=Null,
)
AS
SELECT DISTINCT pkUserID as pkUserID,
firstname + ' ' + lastname + CASE WHEN Degree = 'None'
THEN '' WHEN Degree Is Null Then '' ELSE ', ' +
Degree END as FullName,
Specialty, HomeCity + case WHEN HomeCity = ''
then '' Else ', ' end + HomeState as CityState,
HomePhone as Phone, HomeLatitude, HomeLongitude
FROM users LEFT OUTER JOIN Specialty on
users.specCode = specialty.speccode
WHERE users.HomeLongitude Between @LongWest AND @LongEast
AND users.HomeLatitude Between @LatSouth AND @LatNorth
And Specialty.Specialty = ISNULL(@MedSpec, Specialty)
AND Users.Degree = ISNULL(@Degree, Degree)

There are some rows which should appear when this query is run but do not. The issue appears to be with the Degree column. If the Degree column is NULL and I don't pass in the @Degree parameter (or pass it as NULL) then the row don't appear. If I add a value to the Degree column, regardless of whether I pass in the @Degree parameter, the data rows I am looking for are returned.

Is there any way to accomplish this...without a bunch of dynamic SQL?

thanks...
- dw

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-28 : 16:18:31

AND (@Degree is null OR Users.Degree = @Degree)

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-29 : 00:00:46

In SQL, NULL = NULL does not evaluate to TRUE. So it follows that

WHERE condition....
AND Users.Degree = Users.Degree

evaluates to FALSE when Users.Degree contains a NULL.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2003-11-29 : 03:12:47

Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources