Conversion error when converting nvarchar value to int data type

Question

Do you know what might be wrong here?

All variables are nvarchar. The error occurs when @FunctionValue contains 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, it assumes that you are doing a numeric addition, not a string concatenation.

If your source data is characters, you can fix them by completely removing the cast:

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

If your source data is numeric, 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 "cast to int" outside coalesce() .

Answer 2

You convert 'varchar' and 'int' without explicit type conversion. When this happens, the data type with the highest priority wins. In this case, Int takes precedence over varchar, so the whole statement becomes Int. And converting int to varchar is implicitly not allowed.

Try wrapping "CAST ... like VARCHAR" around your Int values:

 CAST(COALESCE(CAST(@FunctionValue AS INT), CAST(@Value AS INT)) AS NVARCHAR(255)) 

For a list of data type priorities, see http://technet.microsoft.com/en-us/library/ms190309(v=sql.105).aspx

Hope this helps