Adding NULL to an integer to get the integer result in SQL server

Question

I have say a table, the columns of which will contain integer value but i am not sure if they may contain NULL too. Now lets say I want to add the integers, (along with any NULL) to get the result as an integer even when any column in between is NULL.

I am aware of the ISNULL() function and COALESCE() function. But i would prefer not to use any of these. Nor i want to use CASE. I know that CASE would do what i want, but I am looking for something more better.

Just like we have CONCAT_NULL_YIELDS_NULL to ensure that when a string is concatenated to a null value, the result is not null.

I want something similar to this option. Thanks in advance.

Could you explain *why* you don't want to use the functions that are designed for this problem? — Hans Keﬆing, Jul 15 '14 at 11:08
May I ask what the reason for you is, not to use isnull as it's made for what you want to do? — schlonzo, Jul 15 '14 at 11:09
`ISNULL()` and `COALESCE()` are designed for doing what you need to do. How do you intend to beat that? — Sergey Kalinichenko, Jul 15 '14 at 11:09
Every other solution would just be a hack, why not just use those functions? — DavidG, Jul 15 '14 at 11:17
I know that isull and coalesce would do this. But i was asking if there's any other way!! — vstandsforvinay, Jul 15 '14 at 11:18

score 1 · Answer 1 · answered Jul 15 '14 at 11:20

1

You should probably just use those functions. Assuming you can't:

SELECT SUM(MyIntCol) --filters out NULLs automatically

SELECT CASE WHEN MyIntCol IS NOT NULL THEN MyIntCol ELSE 0 END --default value 0

answered Jul 15 '14 at 11:20

usr

168,620
35
240
369

But, i guess there's a problem. I want to add the values which are in different columns. – vstandsforvinay Jul 15 '14 at 11:28
You can use the second code line I posted for that. – usr Jul 15 '14 at 11:31
But, i thought that I mentioned not to use CASE even. Anyways, thanks for telling that sum() would ignore nulls... – vstandsforvinay Jul 15 '14 at 11:42
1

OK, I did not see that. Your question has no answer, then. – usr Jul 15 '14 at 11:44

schlonzo · Answer 2 · 2014-07-15T12:17:35.877

1

This isn't really an answer to you but it might be a solution for people running MS SQL Server 2012 or newer.

SELECT IIF(<NullableColumn1> is null, 0, <NullableColumn1>)
+ IIF(<NullableColumn2> is null, 0, <NullableColumn12) ...
FROM <YourTable>

But I assume, that the engine will do the same what is done, when there is CASE used instead.

edited Jul 15 '14 at 12:17

answered Jul 15 '14 at 12:10

schlonzo

1,409
13
16

Btw. `IIF` is indeed translated into case: http://msdn.microsoft.com/en-us/library/hh213574.aspx – Martin K. Jul 15 '14 at 14:14
@usr actually this was not my intention. I just wanted to know if there's any other way except for those i have mentioned. – vstandsforvinay Jul 16 '14 at 07:16

Adding NULL to an integer to get the integer result in SQL server

2 Answers2