CODEWITHSUNDEEP
sqlsql-server-2008t-sqlstored-procedures
MisterIsaak
MisterIsaak

Reputation: 3922

How to default NULL to Zero

I'm creating sprocs with some calculations and I want to make sure I'm not missing something simple.

Say I'm finding a SUM() of a column that might have NULLs. Is there a single set statement that will convert NULL to Zero automatically without having to COALESCE each time? Or do I have to manually check for NULL each time?

I've looked through MSDN SET but I don't see anything useful.

There's a way to make NULL work with concatenation but I don't see anything for calculations.

For example:

SET ANSI_NULLS ON
SET CONCAT_NULL_YIELDS_NULL ON

--Calc
SELECT SUM(CONVERT(decimal(10,2), NULL))
SELECT SUM(CONVERT(decimal(10,2), Coalesce(NULL,0)))

--Concat
SELECT  NULL + ', ' + 'Isaak' AS Name
SELECT COALESCE(NULL + ', ' + 'Isaak','') AS Name
SELECT COALESCE(NULL,'') + ', ' + 'Isaak' AS Name

--Change Concat NULL to OFF
SET ANSI_NULLS ON
SET CONCAT_NULL_YIELDS_NULL OFF

--Calc
SELECT SUM(CONVERT(decimal(10,2), NULL))
SELECT SUM(CONVERT(decimal(10,2), Coalesce(NULL,0)))

--Concat
SELECT  NULL + ', ' + 'Isaak' AS Name
SELECT COALESCE(NULL + ', ' + 'Isaak','') AS Name
SELECT COALESCE(NULL,'') + ', ' + 'Isaak' AS Name

Upvotes: 0

Views: 2350

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

No, there is no magic way to do this. However there are multiple workarounds:

  1. Stop allowing NULLs in the first place - add a default of 0 and if you can't update the DML logic then add a trigger (but far preferable to do this as part of the original insert/update).
  2. Put the COALESCE into a view, and then reference the view in your queries.
  3. Persist a zero (using COALESCE of course) into a separate, computed column, and change the calculation to use the computed column instead of the original column.

Upvotes: 2

Related Questions