Conversion failed when converting the nvarchar value to data type int

Question

Do you know what could be wrong here?

All variables are nvarchar. The error occurs when @FunctionValue contains an INT in string format.

IF @TargetType = 'INT'
BEGIN
    SELECT @SQLSTR = 'UPDATE ' + @TargetTable + 
                        ' SET ' + @TargetColumn + ' = ' + COALESCE(CAST(@FunctionValue AS INT), CAST(@Value AS INT)) + 
                         ' '
END

Answer 1

The problem is the ambiguity of the + operator. When any argument is numeric, then it assumes you are doing numeric addition, rather than string concatenation.

If your original data is characters, then you can fix it by removing the cast entirely:

IF @TargetType = 'INT'
BEGIN
    SELECT @SQLSTR = 'UPDATE ' + @TargetTable + 
                        ' SET ' + @TargetColumn + ' = ' + COALESCE(@FunctionValue, @Value) + 
                         ' '
END;

If your original data is numeric, then you need to explicitly convert them to characters:

IF @TargetType = 'INT'
BEGIN
    SELECT @SQLSTR = 'UPDATE ' + @TargetTable + 
                        ' SET ' + @TargetColumn + ' = ' + cast(cast(COALESCE(@FunctionValue, @Value) as int) as varchar(255)) + 
                         ' '
END;

I also moved the "cast to int" outside the coalesce().