Auto Null to 0

[Malcav]

Hi
I am sure the answer to this is already in here somewhere but it wont let me search the forum.
I have inherited a lot of sql code where columns are added together. However recently the tables have changed to allow nulls. Thus all the addition is returning nulls.
I know there is something that I can put in the code that will make all the nulls act like 0 during addition.
It will take a very long time to go through all the code and add isnull(,0) to almost every other line.
Thanks for any help.

 

[edsuk]

To substitute a zero value for a NULL:

ISNULL(TableName.ColumnName1,0) + ISNULL(TableName.ColumnName2,0)

 

[Malcav]

Yes I know that (see the bottom of the original post)
Thats what I am trying to avoid.
There is a setting such as :

SET [Blah] on

that will treat all null as zero in any following arithmatics.

 

[gmmastros]

I don't think there is a setting for this. Perhaps you were thinking of....

SET CONCAT_NULL_YIELDS_NULL OFF

Normally, when you concatenate strings where one of them is NULL, the result is null.

Ex:

[COLOR=blue]SET[/color] CONCAT_NULL_YIELDS_NULL [COLOR=blue]ON[/color]

[COLOR=blue]Select[/color] [COLOR=red]'Hello World'[/color] + NULL

[COLOR=blue]SET[/color] CONCAT_NULL_YIELDS_NULL [COLOR=blue]OFF[/color]

[COLOR=blue]Select[/color] [COLOR=red]'Hello World'[/color] + NULL

As far as I know, there is no such setting that will work for numbers.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Malcav]

Hmmmmm I swear i did it once. Its ok now anyway have a grunt going through the code putting isnull(,0) on ever other line.
Thanks for the help.