SQL 将 nvarchar 值转换为数据类型 int 时转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16541239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conversion failed when converting the nvarchar value to data type int
提问by hermann
Do you know what could be wrong here?
你知道这里有什么问题吗？
All variables are nvarchar. The error occurs when @FunctionValue contains an INT in string format.
所有变量都是 nvarchar。当@FunctionValue 包含字符串格式的 INT 时会发生错误。
IF @TargetType = 'INT'
BEGIN
SELECT @SQLSTR = 'UPDATE ' + @TargetTable +
' SET ' + @TargetColumn + ' = ' + COALESCE(CAST(@FunctionValue AS INT), CAST(@Value AS INT)) +
' '
END
采纳答案by Gordon Linoff
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().
我还将“cast to int”移到了coalesce().
回答by Rodders
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.
您正在转换 'varchar' 和 'int' 而不显式转换类型。发生这种情况时，优先级最高的数据类型获胜。在这种情况下，Int 的优先级高于 varchar，因此整个语句变成了 Int。并且不允许将 int 隐式转换为 varchar。
Try wrapping a 'CAST ... as VARCHAR' around your Int values:
尝试在您的 Int 值周围包裹一个 'CAST ... as VARCHAR'：
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
有关数据类型优先级的列表，请参阅http://technet.microsoft.com/en-us/library/ms190309(v=sql.105).aspx
Hope this helps
希望这可以帮助
