Replace NULL with 0 in aquery

I use dynamic SQL in stored procedure to calculate values and update a table

SET @SQLStatement = 'UPDATE CAV_CalcValues '
SET @SQLStatement = @SQLStatement + 'SET '+@CAV_Column + '= '+@Formula
SET @SQLStatement = @SQLStatement + ' FROM KRE_KeyRatioEuro KRE INNER JOIN CAV_CalcValues CAV'
SET @SQLStatement = @SQLStatement + ' ON KRE.KRE_COM_id = CAV.CAV_COM_id AND KRE.KRE_TIM_id = CAV.CAV_TIM_id'
SET @SQLStatement = @SQLStatement + ' JOIN KRX_KeyRatioXXEuro KRX ON KRE.KRE_COM_id = KRX.KRX_COM_id '
SET @SQLStatement = @SQLStatement + ' WHERE KRE.KRE_COM_id IN ('+@CompanyId+')'
SET @SQLStatement = @SQLStatement + ' AND KRX.KRX_COM_id IN ('+@CompanyId+')'
SET @SQLStatement = @SQLStatement + ' AND KRE.KRE_quarter = 0'
EXEC(@SQLStatement)

Basically I have an Update clause where @Formula
can look like the following:

((KRE_MED_181)-(KRE_MED_14)-(KRE_MED_17))*(1-(KRX_MED_1))+(-(CAV_MED_199)-(KRE_MED_15)-(CAV_MED_200))

(different columns in different tables)

The problem is that in the tables from where I take the values the values are allowed to be NULL but in formulas like above the resulting value will be set to NULL if only one value is NULL and I do not want that. Instead I want a null value be set to 0 so I get a value from the rest of the values in the formula.

(13 + NULL + 26)= NULL

But instead I want to replace NULL=0
(13 + 0 + 26)= =39

I know I can change the formula with a lot of CASE clauses to eliminate the NULL value and replace with 0. But I have like 200 formulas and it would take a long time to accomplish that.

My question is, if it possible to in the stored procedure set something that automatically will replace a NULL value with a 0 in an easy way?

This will do the trick:

SELECT IsNull(Field1, 0) + IsNull(Field2, 0)

--
Frettmaestro
"Real programmers don't document, if it was hard to write it should be hard to understand"

No offence Frank, your solution will most definitely work, but I find it to be unnecessarily complicated for the task at hand. The IsNull built-in function works perfectly for this purpose:

(IsNull(KRE_MED_181, 0)-IsNull(KRE_MED_14, 0)-IsNull(KRE_MED_17, 0))*(1-IsNull(KRX_MED_1, 0))+(-IsNull(CAV_MED_199, 0)-IsNull(KRE_MED_15, 0)-IsNull(CAV_MED_200, 0))

--
Frettmaestro
"Real programmers don't document, if it was hard to write it should be hard to understand"

Yes as LuisMartin write. I would like something automatic in my stored procedure. <br /><br />I really wanted to be sure that nothing else can be done but changing the formulas to deal with this problem. That no secret way can be done. My users have asked for this and asked about the time to fixed this. So if 200 formulas need to be updated I have to say that it will take some time to go through those.<br /><br />Thnaks anyway and I feel more comfortable to say the truth to my users <img src='/community/emoticons/emotion-1.gif' alt='' />

Ok, I missed the part about the 200 formulas actually, I must read more carefully the next time I guess <img src='/community/emoticons/emotion-5.gif' alt='' /> Something which doesn't actually sound applicable in this scenario but I find worth mentioning is to set a default value of 0 in the database and disallow NULLS. I am aware that you said NULLS are allowed and I don't know the nature of you datasource, but in an isolated case this would solve your problem in a minimal amount of time.<br /><br />--<br />Frettmaestro<br />"Real programmers don't document, if it was hard to write it should be hard to understand"

No 0 is not the same as NULL in the source tables. Because a NULL value represent that the value does not exist for that specific report in my case. Hence the value can exist for an other report. But a value can be 0 and so forth exist for the report. So to replace all NULL values with 0 is not a possible solution. What is irritating is that a single NULL value in a formula destroys the whole formula.

Interesting that when using

SELECT SUM(KRE_MED_1) FROM ...
returns something if at least one value is <> NULL
But

SELECT ((KRE_MED_1)+(KRE_MED_2)+(KRE_MED_3)) FROM ....

Does not work if a single value is NULL

I think I need to update the formulas

Great forum, and great discussions!

Well FrankKalis. I agree with you, but Microsoft should implement something that can be set in a Stored Procedure that make it possible to ignore NULL and set them to 0 if I would like that so adding values with a possible NULL value won't destroy the calculation.

tmarko: search & raplace with regular expressions can work miracles <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />--<br />Frettmaestro<br />"Real programmers don't document, if it was hard to write it should be hard to understand"

Frank,

I don't think CONCAT_NULL_YIELDS_NULL will help in this situation. That only works for string concatenation, not with math functions.

Example:

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT NULL + 5

returns NULL

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT NULL + '5'

returns '5'

I think the only option in this case is the change the queries using CASE, ISNULL, or COALESCE.

Yes tried

SET CONCAT_NULL_YIELDS_NULL OFF

No change

Thanks for the enthusiasm in this forum

You want to coalesce each value with 0, as in:
((COALESCE(KRE_MED_181,0))-((COALESCE(KRE_MED_14,0))-((COALESCE(KRE_MED_17,0)))*(1-((COALESCE(KRX_MED_1,0)))+(-((COALESCE(CAV_MED_199,0))-((COALESCE(KRE_MED_15,0))-((COALESCE(CAV_MED_200,0)))
There are two ways you can do that - string parse the formula and find variables (text which isn't ()+- ) and replace variable with COALESCE(variable,0).

The other way is to get your formula fields from the database schema. I believe this will work, although I don't have access to your tables to test:

declare @formula varchar(200)
set @formula = '((KRE_MED_181)-(KRE_MED_14)-(KRE_MED_17))*(1-(KRX_MED_1))+(-(CAV_MED_199)-(KRE_MED_15)-(CAV_MED_200))'

declare @column_name varchar(100)

DECLARE columns_cursor CURSOR FOR
select column_name from information_schema.columns where table_name in ('KRE_KeyRatioEuro','CAV_CalcValues CAV','KRX_KeyRatioXXEuro')

OPEN columns_cursor

FETCH NEXT FROM columns_cursor
INTO @column_name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @formula = REPLACE(@formula,@column_name,'COALESCE(' + @column_name + ',0)')
END

CLOSE columns_cursor
DEALLOCATE columns_cursor
GO

oops, missed fetch next in cursor loop:

declare @formula varchar(200)
set @formula = '((KRE_MED_181)-(KRE_MED_14)-(KRE_MED_17))*(1-(KRX_MED_1))+(-(CAV_MED_199)-(KRE_MED_15)-(CAV_MED_200))'

declare @column_name varchar(100)

DECLARE columns_cursor CURSOR FOR
select column_name from information_schema.columns where table_name in ('KRE_KeyRatioEuro','CAV_CalcValues CAV','KRX_KeyRatioXXEuro')

OPEN columns_cursor

FETCH NEXT FROM columns_cursor INTO @column_name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @formula = REPLACE(@formula,@column_name,'COALESCE(' + @column_name + ',0)')
FETCH NEXT FROM columns_cursor INTO @column_name
END

CLOSE columns_cursor
DEALLOCATE columns_cursor
GO