Passing NULL Parameters-Concise code

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)

Passing NULL Parameters-Concise code

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-26 : 16:15:30

It seems that whenever I have a situation where a parameter is passed to an SP, and that parameter can have a valid null value, that my procedure becomes quite lengthy with lots of IF...ELSE structure. As a simple example,

the following code takes a nullable input parameter @HometownId

IF NOT IS NULL @Hometown
BEGIN
SELECT * FROM MyTable WHERE HomeTown=@HomeTownId
END
ELSE
BEGIN

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-26 : 16:18:41

Sorry... posted by accident (darn default buttons).... Any ways, the full code is...

IF @Hometown IS NOT NULL
BEGIN
SELECT * FROM MyTable WHERE HomeTown=@HomeTownId
END
ELSE
BEGIN
SELECT * FROM MyTable WHERE HomeTown IS NULL
END

This obviously leades to longer and more complex code. Does anyone know of a way to achieve the same result in amore cocise way? Thanks.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-07-26 : 16:25:20

a CASE statement maybe?

-ec

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-26 : 16:29:10

Well, CASE or IF..ELSE, it still leads to the more complex code. I was hoping there was some sort of Built in Function in SQL Server. The problem is that [tablefield]=NULL is not the same as [tablefield] IS NULL. I suppose the developers of T-SQL had good reason for this, but it sure causes a few minor problems.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 11:54:11

Try

SELECT * FROM MyTable WHERE (HomeTown=@HomeTownId or @HomeTownId is null)

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-07-27 : 12:58:05

quote:
Originally posted by madhivanan

Try

SELECT * FROM MyTable WHERE (HomeTown=@HomeTownId or @HomeTownId is null)

Madhivanan

Failing to plan is Planning to fail

If @hometowndid is null then this will return that entire table !

Will this work?:
where isNull(hometownid,-69) = isNull(@hometownid,-69)

Be One with the Optimizer
TG

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-27 : 14:18:06

Or this?...

SELECT * FROM MyTable
where (HomeTown = @HomeTown or (@HomeTown is null and HomeTown is null))

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-28 : 12:13:28

Thanks for the correction TG

Madhivanan

Failing to plan is Planning to fail

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-28 : 12:33:44

Thanks for all your suggestions. I went with

SELECT * FROM MyTable
where (HomeTown = @HomeTown or (@HomeTown is null and HomeTown is null))

which appears to work and is certainly more concise than all those IF...THEN trees!

Kristen
Test

22859 Posts

Posted - 2006-07-29 : 05:01:44

I hate changing the setting for ANSI_NULLS, but it might offer a solution - i.e. HomeTown = @HomeTown would be TRUE if both were NULL

Kristen

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-30 : 09:37:20

That is also an interesting solution. What are the drabacks to turning ansi nulls off. I thought it only made it so you had to explicitly define a nullable field as such.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-07-30 : 10:02:13

SELECT * FROM MyTable
where HomeTown = @HomeTown or coalesce(@HomeTown, HomeTown) is null

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-30 : 10:12:34

it seems to me that the coalesce function will give the same results as (@HomeTown is null and HomeTown is null). While the code is slightly more concise, wouldn't the performance be slightly worse due to the need to call an intrinsic function?

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-30 : 20:55:59

With mine playing around with coalesce() it seems that it gets translated into an implicit CASE (the optimizer calls it IF/ELSE though) construct and not a function call, so I doubt it affects performance.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Kristen
Test

22859 Posts

Posted - 2006-07-31 : 04:48:56

WHERE COALESCE(NullIf(HomeTown, @HomeTown), @HomeTown~~, HomeTown~~) IS NULL

Qualifies as, what we call here, "Write only code"

I think you can skip the final [HomeTown] parameter, but I haven't thought about it too deeply.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources