将varchar值'A001'转换为数据类型int时出错

I'm developing a SQL Server 2016 SP2 stored procedure that generates a JSON string.

我正在开发一个生成JSON字符串的SQL Server 2016 SP2存储过程。

When I run it, this piece of code:

当我运行它时,这段代码:

set @batch = (
    select 1 as [BatchId], Value as [Name], (CAST(SYSDATETIMEOFFSET() as varchar(34))) as [Created]
        from VariableData
        where VariableData.ProductionOrderId = @productionOrderId and VariableData.VariableDataId = 10
    for json path, WITHOUT_ARRAY_WRAPPER
);

With these data:

有了这些数据:

Generates the following error:

生成以下错误:

Error when converting the varchar value 'A001' to the data type int

将varchar值'A001'转换为数据类型int时出错

But I don't understand why I get that message because I don't use VariableDataId column in the above sql code.

但是我不明白为什么我得到那条消息因为我在上面的sql代码中没有使用VariableDataId列。

If there isn't any char in column VariableDataId (of type varchar(4)), I don't get any error.

如果列VariableDataId(类型为varchar(4))中没有任何字符,则不会出现任何错误。

Why do I get that error message?

为什么我会收到该错误消息?

2 个解决方案

#1

But I don't understand why I get that message because I don't use VariableDataId column in the above sql code.

但是我不明白为什么我得到那条消息因为我在上面的sql代码中没有使用VariableDataId列。

Actually you are, in the where condition VariableData.VariableDataId = 10. It is causing an implicit conversion to int for all values in that column.

实际上,在条件VariableData.VariableDataId = 10的情况下,它会导致对该列中的所有值进行隐式转换为int。

Just modify the comparison to VariableData.VariableDataId = '10' (where '10' is a string/varchar), and the implicit conversion will not be performed.

只需将比较修改为VariableData.VariableDataId ='10'(其中'10'是字符串/ varchar),并且不会执行隐式转换。

#2

You refer to VariableDataId column in line 4 of your query

您可以在查询的第4行中引用VariableDataId列

where VariableData.ProductionOrderId = @productionOrderId and VariableData.VariableDataId = 10

You have two options. First, use the correct data type in the predicate.

你有两个选择。首先,在谓词中使用正确的数据类型。

where VariableData.ProductionOrderId = @productionOrderId and VariableData.VariableDataId = '10'

Second, convert column with TRY_CAST/TRY_CONVERT if you want to use INT value as the condition.

其次,如果要使用INT值作为条件,请使用TRY_CAST / TRY_CONVERT转换列。

where VariableData.ProductionOrderId = @productionOrderId and TRY_CAST(VariableData.VariableDataId AS INT) = 10

I recommend the first option.

我推荐第一个选项。

#1