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.

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)

About these ads

9 thoughts on “SQL SERVER – SSMS does NOT Print NULL Values

  1. 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.

    Like

  2. You would get a printed result if you were to do the following:

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

    Like

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

      Like

  3. 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’)

    Like

  4. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s