T-SQL: 在 THEN 语句中将数据类型 varchar 转换为 numeric 出错。

Question

我无法理解这个错误，因为我没有尝试将变量@FQTROQ转换为数字。

declare @FQTROQ varchar(30)
declare @FQNUTQ decimal(6,0)
set @FQTROQ = 'R-354'
set @FQNUTQ = 100

SELECT ( CASE WHEN (@FQTROQ is not null and @FQTROQ <> '')
THEN ( @FQTROQ )
ELSE ( @FQNUTQ ) END ) AS Numero_Troquel

有需要帮助的吗？谢谢。

- user354427

Answer 1

declare @FQTROQ varchar(30)
declare @FQNUTQ decimal(6,0)
set @FQTROQ = 'R-354'
set @FQNUTQ = 100

SELECT CASE WHEN (@FQTROQ is not null and @FQTROQ <> '')
THEN @FQTROQ
ELSE CAST(@FQNUTQ AS VARCHAR(30)) END AS Numero_Troquel

你需要将DECIMAL转换为VARCHAR，以便CASE语句的输出具有相同的输出值。

- Neil Knight

---

我曾经遇到过类似的情况，我试图根据列内容有条件地将列转换为十进制。在将我的十进制结果强制转换回 varchar 之前，我一直遇到这个错误。非常感谢！ - cBlaine

Answer 2

你需要做这个：

SELECT ( CASE WHEN (@FQTROQ is not null and @FQTROQ <> '')
THEN ( @FQTROQ )
ELSE ( CAST(@FQNUTQ AS VARCHAR(30))) END ) AS Numero_Troquel

因为需要匹配列返回的值，SQL Server 将尝试将您的 VARCHAR 转换为 NUMERIC，而不是您可能想要的反向转换。

顺便问一下，那个命名约定是怎么回事？

- Dave Markle

Answer 3

这是因为您有两个不同类型的变量，即使您只想将其中一个值作为“Numero_Troquel”返回，它们也必须是兼容的类型。它正在尝试将@FQTROQ变量转换为DECIMAL。

您需要执行以下操作之一：

SELECT ( CASE WHEN (@FQTROQ is not null and @FQTROQ <> '')
THEN ( @FQTROQ )
ELSE ( CAST(@FQNUTQ AS VARCHAR(30)) ) END ) AS Numero_Troquel

或者...

IF (@FQTROQ is not null and @FQTROQ <> '')
    SELECT @FQTROQ AS Numero_Troquel -- this will return as a VARCHAR
ELSE
    SELECT @FQNUTQ AS Numero_Troquel -- this will return as a DECIMAL

- AdaTheDev

---

非常感谢。简单的答案我不知道。 - user354427