April 24, 2023 at 12:00 am
Comments posted to this topic are about the item Summing a BIT column with a numeric as the outcome
Cheers, Tonie
See you at PASS this year?
https://passdatacommunitysummit.com/speakers/TonieHuizer
April 24, 2023 at 7:40 am
I think that the question is bad worded! So, the sentence "The software using the outcome of the code would always expect a numeric value" should be "The software doesn't handle NULL value". All of the three answers return an INT, but only one returns a column NOT NULLABLE.
April 24, 2023 at 7:57 am
Carlo, you are right!
use tempdb
go
CREATE TABLE NewTable (record BIT);
GO
DECLARE @tsql nvarchar(max);
Set @tsql = N'SELECT SUM(CAST(record AS INT)) from NewTable';
SELECT * FROM sys.dm_exec_describe_first_result_set(@tsql, null, 0);
Set @tsql = N'SELECT SUM(ISNULL(CAST(record AS INT), 0)) from NewTable';
SELECT * FROM sys.dm_exec_describe_first_result_set(@tsql, null, 0);
Set @tsql = N'SELECT ISNULL(SUM(CAST(record AS INT)), 0) from NewTable';
SELECT * FROM sys.dm_exec_describe_first_result_set(@tsql, null, 0);
go
DROP TABLE NewTable ;
Have a nice day,Christoph
April 24, 2023 at 10:11 am
Sorry folks, but the QoD submission some how went wrong.
This was the original explanation
TL;DR; Put a ISNULL around the SUM to ensure a numeric value is always provided, even in case of an empty table.
Below three tests to figure out what will work in all situations.
DECLARE @table AS TABLE
(
record BIT
);
-- test run numeric values and NULLs
INSERT @table
(
record
)
VALUES
(
1
),
(
NULL
),
(
1
),
(
NULL
),
(
1
);
SELECT * FROM @table
SELECT SUM(CAST(record AS INT)) -- results in the expected numeric value
FROM @table;
SELECT SUM(ISNULL(CAST(record AS INT), 0)) -- results in the expected numeric value
FROM @table;
SELECT ISNULL(SUM(CAST(record AS INT)), 0) -- results in the expected numeric value
FROM @table;
Both an ISNULL around the value and the SUM works.
DECLARE @table AS TABLE
(
record BIT
);
-- new test run with only NULLs
INSERT @table
(
record
)
VALUES
(
NULL
),
(
NULL
),
(
NULL
),
(
NULL
);
SELECT * FROM @table
SELECT SUM(CAST(record AS INT)) -- result is not as expected
FROM @table;
SELECT SUM(ISNULL(CAST(record AS INT), 0)) -- results in the expected numeric value
FROM @table;
SELECT ISNULL(SUM(CAST(record AS INT)), 0) -- results in the expected numeric value
FROM @table;
Both an ISNULL around the value and the SUM works.
DECLARE @table AS TABLE
(
record BIT
);
-- new test with an empty table
SELECT * FROM @table
SELECT SUM(CAST(record AS INT)) -- result is not as expected
FROM @table;
SELECT SUM(ISNULL(CAST(record AS INT), 0)) -- result is not as expected
FROM @table;
SELECT ISNULL(SUM(CAST(record AS INT)), 0) -- results in the expected numeric value
FROM @table;
Only an ISNULL around the SUM works.
Which makes sense, because the table is empty and summing this missing results in a NULL.
So, ISNULL around the SUM is the best option.
Cheers, Tonie
See you at PASS this year?
https://passdatacommunitysummit.com/speakers/TonieHuizer
April 24, 2023 at 1:00 pm
Sorry folks, but the QoD submission some how went wrong.
This was the original explanation
TL;DR; Put a ISNULL around the SUM to ensure a numeric value is always provided, even in case of an empty table.
Below three tests to figure out what will work in all situations.
DECLARE @table AS TABLE
(
record BIT
);
-- test run numeric values and NULLs
INSERT @table
(
record
)
VALUES
(
1
),
(
NULL
),
(
1
),
(
NULL
),
(
1
);
SELECT * FROM @table
SELECT SUM(CAST(record AS INT)) -- results in the expected numeric value
FROM @table;
SELECT SUM(ISNULL(CAST(record AS INT), 0)) -- results in the expected numeric value
FROM @table;
SELECT ISNULL(SUM(CAST(record AS INT)), 0) -- results in the expected numeric value
FROM @table;Both an ISNULL around the value and the SUM works.
DECLARE @table AS TABLE
(
record BIT
);
-- new test run with only NULLs
INSERT @table
(
record
)
VALUES
(
NULL
),
(
NULL
),
(
NULL
),
(
NULL
);
SELECT * FROM @table
SELECT SUM(CAST(record AS INT)) -- result is not as expected
FROM @table;
SELECT SUM(ISNULL(CAST(record AS INT), 0)) -- results in the expected numeric value
FROM @table;
SELECT ISNULL(SUM(CAST(record AS INT)), 0) -- results in the expected numeric value
FROM @table;Both an ISNULL around the value and the SUM works.
DECLARE @table AS TABLE
(
record BIT
);
-- new test with an empty table
SELECT * FROM @table
SELECT SUM(CAST(record AS INT)) -- result is not as expected
FROM @table;
SELECT SUM(ISNULL(CAST(record AS INT), 0)) -- result is not as expected
FROM @table;
SELECT ISNULL(SUM(CAST(record AS INT)), 0) -- results in the expected numeric value
FROM @table;Only an ISNULL around the SUM works.
Which makes sense, because the table is empty and summing this missing results in a NULL.
So, ISNULL around the SUM is the best option.
Hello Tonie, you are confusing data type with nullability. INT always is numeric, also when it is NULL.
April 24, 2023 at 3:07 pm
While answer 3 may meet the overt requirements, it' returns the following "extra" info in the presence of NULL values, as well.
Warning: Null value is eliminated by an aggregate or other SET operation.
Depending on the UI, that can also cause an error just as if a row count were mistakenly returned.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2023 at 11:04 pm
The context of the QoD was a code review of an SP which is used in running .Net software, without propper NULL handling indeed.
An Integer in .NET can't be assigned a NULL value, out of the box, it expects a numeric value. That's where the numeric part of the QoD came from.
But rephrasing the question to your suggestion would prevented this confusion.
I understand, especially from a SQL point of view.
Let me be clear, the developer need to provide a proper error catching for this 😉
Cheers, Tonie
See you at PASS this year?
https://passdatacommunitysummit.com/speakers/TonieHuizer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply