Simple function benefits - 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

General SQL Server Forums

New to SQL Server Programming

Simple function benefits

Author

Topic

ron2112
Starting Member

44 Posts

Posted - 2007-05-18 : 08:29:19

Hey all,

Very simple question from a real n00b: In many of my stored procedures I am repeating a CASE statement where I'm replacing null values with zero, like this:

(CASE WHEN @Field IS NULL THEN 0 ELSE @Field END)

I have many SPs where I have that code repeated literally dozens of times. Is there any performance benefit to creating a function to perform this task and using that instead of the repeated CASE statements? Or am I trying to be too clever?

Thanks!
Ron Moses
ConEst Software Systems

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-18 : 08:31:38

Why create function when there are already functions for this specific purpose?

Make use of COALESCE() or ISNULL() functions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ron2112
Starting Member

44 Posts

Posted - 2007-05-18 : 08:59:13

I'm not quite seeing how COALESCE() would help. If @Field is NULL and I coalesce @Field and 0 I will get 0. But if @Field is a negative value I will also get 0. Like I said, I'm pretty new to all of this so forgive me if I'm missing your point.

ISNULL() returns a boolean, of course, so I would still have to use it in a conditional statement.

To clarify my question, I'm really just looking to find out if I will get any performance benefit from using a formula like this:

CREATE FUNCTION [dbo].[NullToZero_float](@Value float)
RETURNS float
AS
BEGIN
DECLARE @Ret float;
IF @Value IS NULL
SET @Ret = 0
ELSE SET @Ret = @Value
RETURN @Ret
END

...and calling dbo.NullToZero_float(@Field) rather than using CASE statements over and over in my SPs.

Thanks
Ron Moses
ConEst Software Systems

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-18 : 09:07:50

Why don't you take a look at books online and see for yourself.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ron2112
Starting Member

44 Posts

Posted - 2007-05-18 : 09:14:32

No need to be insulting. I do know how to read, but being a human I sometimes miss things. Horrifying, I know.

In fact I just re-read ISNULL() and saw what I missed the first time, that it takes a second replacement parameter. That will work very well, thanks for the recommendation.

Ron Moses

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-18 : 11:24:05

<<
If @Field is NULL and I coalesce @Field and 0 I will get 0. But if @Field is a negative value I will also get 0.
>>

SELECT COALESCE(-10,0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources