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.
When Karthik was trying to run 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.
Reference: Pinal Dave (http://blog.sqlauthority.com)
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.
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.
http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/
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