CODEWITHSUNDEEP
sqlsql-servert-sql
hermann
hermann

Reputation: 6295

Conversion failed when converting the nvarchar value to data type int

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

Upvotes: 5

Views: 48342

Answers (2)

Rodders
Rodders

Reputation: 2435

You are converting a 'varchar' and an 'int' without explicitly converting the types. When this happens, the data-type with the highest precedence wins. In this case, Int has a higher precedence than a varchar, therefore the whole statement becomes an Int. And converting an int to a varchar inexplicitly is not allowed.

Try wrapping a 'CAST ... as VARCHAR' around your Int values: 

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

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

Hope this helps

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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().

Upvotes: 4

Related Questions