Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any good way to avoid writing so many times isnull() function inside sproc ?
I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I find better way to get my data ill love to clean my code from so many isnull() functions.
Can I for while set MSSQL 2008R2 server to using null values as float 0.
Moost of my isnull()-s adding just zero 0 value if there is no data so I can do maths operations.

EDIT: I am not lazy, I just trying to clean my code and avoid select parts looks like this

Well, why are you using so many isnull() functions? Your application should be able to deal with NULL just fine, or perhaps you can avoid this by not allowing NULL in the first place. NULL is not the same thing as 0, but if you're going to treat it that way, it may as well.
–
Aaron Bertrand♦Apr 26 '13 at 14:09

@AaronBertrand I do calculations (+,-,*) inside TSQL but my tables are mostly Left Outer Joined and I have many nulls
–
adopilotApr 26 '13 at 14:13

Well there is no magic setting that turns all NULL values into zeroes, sorry.
–
Aaron Bertrand♦Apr 26 '13 at 14:13

@AaronBertrand Thanx on that, I was hoping that I can set ANSII NULL param but looks like it ANSII NULL is not in common whit my problem
–
adopilotApr 26 '13 at 14:17

1

ANSI_NULLS considers how NULL values are evaluated (e.g. WHERE col = NULL vs. WHERE col IS NULL). It has nothing to do with how NULL float values might be coerced into becoming zero.
–
Aaron Bertrand♦Apr 26 '13 at 14:19

3 Answers
3

No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.

I have a similar query showing a TON of encapsulating ISNULL statements. I need the null values to represent items that have not been touched (as many columns required distinct identifiers for alterations). I tried a million different things to get around it and in the end just ended up with an annoyingly long code laced with a bunch of ISNULLs. The important thing about the 'annoyingly long coded laced with a bunch of ISNULLs' is that it works perfect for me every time. I would suggest just finding a pattern to using them to clean up the look of the code and make it easier to navigate in general.

As far as the suggestion to use COALESCE, I wouldn't suggest it depending on the amount of records you are trying to return. I have read over and over again that COALESCE can cause a hit to performance, though there are some benefits.

All of the things you've read over and over again point to performance differential theories with no actual data to back them up. Have you read this link, where I actually do performance tests, and also explain the many other benefits?
–
Aaron Bertrand♦Apr 26 '13 at 20:23