Need to explain that ISNULL can truncate the replacement_value - by John Paul Cook

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
2
Sign in
to vote
ID 778704 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 2/6/2013 4:32:15 PM
Access Restriction Public

Description

DECLARE @msg    NVARCHAR(4000);
DECLARE @str1   NCHAR(1);
SET @msg = N'@str1 is ' + ISNULL (@str1, N'not defined');
SELECT @msg;

output is:

@str1 is n

Only those who read BOL or http://msdn.microsoft.com/en-us/library/ms184325.aspx very carefully will understand that this is documented behavior. You need to explicitly tell people that if the replacement_value is longer than check_expression, it will be truncated to the length of the check_expression.

There are many database developers who don't understand this after reading the documentation.
Sign in to post a comment.
Posted by Microsoft on 2/12/2013 at 9:50 AM
Good suggestions. I have added a truncation warning in the remarks section. And added a stronger reference to COALESCE. This will take a while to appear on MSDN. Thank you.