NULLs and SQL SERVER... details matter!

While refactoring a function made  by a developer , I came across a situation where the COALESCE was not helping to prevent null values​​. In case it was an assignment of value to a variable based on a query.

Observe the test code below:

CREATE TABLE #teste 
  ( 
     codigo INT 
  ) 

DECLARE @codigo INT; 

SELECT @codigo = COALESCE(codigo, 0) 
FROM   #teste 

SELECT @codigo AS [Retornando NULO] 

DROP TABLE #teste 

If you run the above code, notice that the result is null, even using the coalesce function. In case, the developer decided issue by adding a check with IF testing checking if the variable is null.

This is not wrong, but I think we should reduce the flow of unnecessary code. We can work around the problem by adding the default value in the variable declaration. Thus, if the query returns nothing, the variable retains the original value.

CREATE TABLE #teste 
  ( 
     codigo INT 
  ) 

INSERT INTO #teste 
VALUES      (1) 

DECLARE @codigo2 INT = 0; 

SELECT @codigo2 = COALESCE(codigo, 0) 
FROM   #teste 

SELECT @codigo2 AS [Retornando ZERO] 

DROP TABLE #teste

Check out my new book on MariaDB http://www.amazon.com/dp/B00MQC06HC