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

SQL SERVER - SSMS Does NOT Print NULL Values NullDisplay1

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.

SQL SERVER - SSMS Does NOT Print NULL Values NullDisplay2

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.

SQL SERVER - SSMS Does NOT Print NULL Values NullDisplay3

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:

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

, , , ,
Previous Post
SQL SERVER – Weekly Series – Memory Lane – #018
Next Post
SQL SERVER – Exporting Query Results to CSV using SQLCMD

Related Posts

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

    Reply
  • 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.

    Reply
  • You would get a printed result if you were to do the following:

    SELECT @var4=(coalesce(@var1, 0)+coalesce(@var2, 0)+coalesce(@var3, 0))

    Reply
  • Ali Kolahdoozan
    March 5, 2013 6:23 am

    ISNULL Dear.Isnull will solve the problem.

    Reply
    • Asesh Ghosal
      July 19, 2013 5:24 pm

      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!

      Reply
  • Interesting

    Reply
  • 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.

    Reply
  • 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’)

    Reply
  • 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

    Reply

Leave a Reply

Menu