t-sql：将数据类型varchar转换为数字在语句中时出错

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 

任何帮助？感谢

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语句的输出具有相同的输出值。

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转换为数值，而不是其他方式，这是什么你可能想要。

这是什么命名约定，顺便说一句？

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