Unexpected function behavior ISNULL.

Luiz Lenire 21 Reputation points

Good evening everyone, I came across the following scenario:

As you can see, using the ISNULL function, for some reason that is NOT coherent, it assigns the size of the first field informed, as the final display size, erroneously cutting the text.

I am using Microsoft SQL Server Management Studio v18.11.1

Due to this evidenced scenario, now I need to review all the queries I created using this resource, because the information presented to the end user does not proceed with what it should actually display.

Have you ever had this kind of problem?

The intention of opening this discussion is to know if it is possible to make a correction on top of this function.

Thank you very much for your attention.

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,459 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 17,156 Reputation points Microsoft Vendor

    Hi @Luiz Lenire

    This is an erroneous behavior of the function.

    Actually, this is a setting of the function itself, not an error behavior. ISNULL uses the data type of the first argument.
    Refer to Microsoft doc: ISNULL (Transact-SQL)
    As is shown on this image, you could use COALESCE which is a syntactic shortcut for the CASE expression.

    Best regards,
    Li Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

2 additional answers

Sort by: Most helpful
  1. Jingyang Li 5,806 Reputation points

    You have code error for your nvarchar size.
    It should be @operatoreleven _CODE nvarchar(200) not nvarchar(020).

    From Microsoft documentation:
    ISNULL ( check_expression , replacement_value )
    Is the expression to be checked for NULL. check_expression can be of any type.

    Is the expression to be returned if check_expression is NULL.
    replacement_value must be of a type that is implicitly convertible to the type of check_expression.

  2. Yitzhak Khabinsky 24,011 Reputation points

    Hi @Luiz Lenire ,

    You can use COALESCE() function for your needs.
    ISNULL() is a proprietary MS SQL Server function
    COALESCE() is an SQL standards based function, and supported by other RDBMSes.

    Please see below.


    DECLARE @batch_code NVARCHAR(20) = NULL  
     , @batch_description NVARCHAR(200) = N'DESCRIPTION_BATCH_TEST';  
    SELECT @batch_code AS batch_code, @batch_description AS batch_description  
     , ISNULL(@batch_code, @batch_description) AS Using_ISNULL_Function  
     , COALESCE(@batch_code, @batch_description) AS Using_COALESCE_Function;  


    | batch_code |   batch_description    | Using_ISNULL_Function | Using_COALESCE_Function |