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)
9 Comments. Leave new
Interesting so having a null value in the mathematics changes the outcome to null, not sure if its correct logic but interesting non the less
Good hint, thanks !
Had this problem myself some time ago when i was concatenating a print string with certain values and it was driving me nuts that there was no reply from SSMS :) – Gonna use ‘Select’ from now on.
You would get a printed result if you were to do the following:
SELECT @var4=(coalesce(@var1, 0)+coalesce(@var2, 0)+coalesce(@var3, 0))
ISNULL Dear.Isnull will solve the problem.
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+ ISNULL(@var2,0)+@var3)
PRINT @var4
I guess this could be a more generalized solution, thanks Ali :) Cheers!
Interesting
For SQL Server, ISNULL will work. However, find me an ISNULL in Oracle and you will see the solution I provide is better for this particular problem.
Here is an article which extensively examines this question.
IsNull can also cause a different problem if you aren’t careful though.
declare @var1 int = NULL
, @var2 varchar(100) = NULL
print ‘@var1=’ + convert(varchar(100), isNull(@var1, ‘NULL’)) –will NOT work, and will throw an exception instead.
but
print ‘@var2=’ + convert(varchar(100), isNull(@var2, ‘NULL’)) –WILL work.(and the convert is unnecessary)
Why? because IsNull() converts the second parameter into the type of the first parameter before returning a value and the character string ‘NULL’ does not convert into an int!
the correct form to safely print @var1 when NULLs are possible would be:
print ‘@var1=’ + isNull(convert(varchar(100), @var1), ‘NULL’)
How about this ?
DECLARE @print NVARCHAR(max)
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)
SELECT @print= CASE WHEN CAST(@var4 AS VARCHAR(10)) IS NULL THEN ‘NULL’ ELSE CAST(@var4 AS VARCHAR(10)) end
PRINT @print