0

It looks like a 2nd variable in an expression is determining the maximum length of my 1st variable.

An example is easier to explian:

DECLARE @MyDest AS NVARCHAR(MAX) = 'Some long text, for testing';
DECLARE @MySrc AS NVARCHAR(6) = '';

SELECT @MyDest = ISNULL(NULLIF(@MySrc, N''), @MyDest);

SELECT @MyDest;

The result is 'Some l' while I would expect 'Some long text, for testing'.

This is a simplified version of a more complicated bug I'm trying to fix. Is this a bug in SQL server or should I be doing this differently?

Improve this question

1 Answer 1

Reset to default
4

It is truncating because the replacement_value parameter is being converted to the same data type as the check_expression.

From https://msdn.microsoft.com/en-us/library/ms184325.aspx

ISNULL ( check_expression , replacement_value )

replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

Return Types Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

Improve this answer
4
  • Yes, the "Check expression" is the nested expression NULLIF(@MySrc, N'') which returns NULL. And if the "Check expression" is a literal NULL (well in this case not literal NULL but a NULL returned from an expression which I think should evaluate the same way as any other NULL), it should return the type of the replacement value, which it does not. I still think that my result is different from what the documentation states.
    – Louis Somers
    Commented May 26, 2016 at 21:42
  • 1
    If you test that by setting @MySrc to a length of 8, DECLARE @MySrc AS NVARCHAR(8) = NULL;, and setting it to NULL it still uses that data type and not the replacement_value type of NVARCHAR(MAX).
    – tpet
    Commented May 26, 2016 at 21:50
  • 1
    Ok, you are correct. I also just read on msdn.microsoft.com/en-us/library/ms177562.aspx that NULLIF returns a typed NULL ("NULLIF returns a null value of the type of the first expression"). So one NULL can be of a different type than another NULL. That explains it. I'll have to work around this somehow (in this example it would be simple, but I'm having difficulty with deeply nested declarative expressions and sometimes wish I could replace them with some procedural code here and there)... any way thanks for clearing that up.
    – Louis Somers
    Commented May 26, 2016 at 22:00
  • Great, best of luck with that.
    – tpet
    Commented May 26, 2016 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.