Multiple usage of functions to ensure “value” is not null? – NO

In this post I’m describing a piece of code that is not well written. The author (unknown) had applied three functions for a variable to ensure it never accepts NULLs. The code is given in the next screen shot.

ScreenShot 1. The query with three functions on OriginalAmount

As you can see the author had used three functions on the OriginalAmount column. I disagree for that approach of using twice the ISNULL function. Additionally the potential replacement of a NULL to 0 (instead of 0.0), forces an unnecessary implicit conversion. The replacement for the above query is the following

DECLARE @TotalDeposit DECIMAL(19,6);
SELECT@TotalDeposit = SUM(t.OriginalAmount)
FROMdbo.Transactions (NOLOCK) t
WHEREt.WalletID = 40689;
SELECT @TotalDeposit;

Query1. Query updated, displaces ISNULL functions and only SUM is used

In order to prove to the author that there is a sufficient appliance of the ISNULL functions, I’m making the next example demo with the data from the referenced tables. First executing the query for an example WalletID = 40689 to take a look on the data from the screen shot below.

ScreenShot2. The query data

Next I’m creating a temp table and populating it with the data for WalletID=40689.

IF OBJECT_ID('tempdb..#tmpTransactions') IS NOT NULL
DROP TABLE #tmpTransactions;
CREATE TABLE #tmpTransactions(
ID BIGINT PRIMARY KEY,
OriginalAmount DECIMAL(19,6),
WalletID BIGINT
);
INSERT INTO #tmpTransactions ( ID, OriginalAmount, WalletID )
SELECT ID,OriginalAmount,WalletID 
FROM dbo.Transactions WHERE WalletID=40689;

Then I check if there are any zero values for the OriginalAmount column and make the following update just to ensure I have NULLs instead of zeros in the data set. I want to make the example closer to a potential real-case scenario when there would be multiple NULLs and for which the author had been potentially worried.

SELECT * FROM #tmpTransactions
WHERE OriginalAmount = 0.0
UPDATE #tmpTransactions
SET OriginalAmount=NULL
WHERE OriginalAmount = 0.0

Running the same query from above produces the same result.

ScreenShot 3. The code-refactored query with the temp table.

In the Messages there is the expected warning for the elimination of the NULLs in aggregated functions.

Warning: Null value is eliminated by an aggregate or another SET operation.

The warning is not changing the results. The query can be re-written in a more elegant way by using only the SUM function.

SET NOCOUNT ON;
DECLARE @TotalDeposit DECIMAL(19,6);
SELECT@TotalDeposit = SUM(t.OriginalAmount)
FROMdbo.Transactions (NOLOCK) t 
SELECT ISNULL(@TotalDeposit,0.0)

Applying two additional functions for such a simple query and with consideration that the Transaction table is counting millions of rows and there can be thousands of transactions for a WalletID, is not a well designed code.

However, if of any reason there is a possibility for obtaining a NULL and to just ensure I really never have that NULL “value” for the @TotalDepost variable I can use the ISNULL function on it in the end and now it won’t touch the performances of the code.