SQL SERVER – SSMS Does NOT Print NULL Values

Here is a very interesting question asked on the blog by Karthik. I really liked the question and I would like to discuss this here about SSMS doe snot Print NULL values.

When Karthik was trying to run the following query he was getting no result on the SSMS.

DECLARE @var1 numeric(10,2),
@var2 numeric(10,2),
@var3 numeric(10,2),
@var4 numeric(10,2)
SET @var1=12500
SET @var2=NULL
SET @var3=500
SELECT @var4=(@var1+@var2+@var3)
PRINT @var4

Here is the screenshot of the query executed. The reason behind no result was @var2 is assigned value to NULL. Later the same @var2 is added to another variables and finally assigned to @var4. Whenever NULL value is added to any other value the resultant values are NULL that leads to no result.

A NULL value can’t be printed using PRINT statement and that is the reason it does not print anything.

However, if you assign @var2 to any other value as displayed in following image – it will display the print value.

Alternatively, if you want to display the output of @var4 when @var2 is NULL, you can use SELECT instead of PRINT and you will be able to display the value of NULL.

I think this was very basic but very interesting question for sure and I like to write about it.

Here are the few of the interesting related blog post on this topic:

• SQL SERVER – Observation – Adding a NULL Column and Table Space
• Can We Have NULL Value in Primary Key? – Interview Question of the Week #075
• What is the Default Datatype of NULL? – Interview Question of the Week #131
• SQL SERVER – Understanding JSON NULL Value Using STRICT Keyword
• Primary Key and Null in SQL Server – Interview Question of the Week #071
• SQL SERVER – How to Change Column Property From NULL to NOT NULL Value?

Reference: Pinal Dave (https://blog.sqlauthority.com)