Unexpected function behavior ISNULL.

Question

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.

Answer 1

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.

Answer 2

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 )
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.

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

Answer 3

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.

SQL

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;  

Output

+------------+------------------------+-----------------------+-------------------------+  
| batch_code |   batch_description    | Using_ISNULL_Function | Using_COALESCE_Function |  
+------------+------------------------+-----------------------+-------------------------+  
| NULL       | DESCRIPTION_BATCH_TEST | DESCRIPTION_BATCH_TE  | DESCRIPTION_BATCH_TEST  |  
+------------+------------------------+-----------------------+-------------------------+