t-sql : Error converting data type varchar to numeric in then statement

Question

Hi, I cannot understand the error because I'm not trying to convert to numeric the variable @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

Any help? thanks

Answer 1

You need to do this:

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

because the returned value from a column needs to match, SQL Server will try to convert your VARCHAR to a NUMERIC, not the other way around, which is what you probably want.

What's with that naming convention, btw?

Answer 2

It's because you have 2 variables of different types, and even though you're only trying to return ONE of those values as "Numero_Troquel", they have to be compatible types. What it's doing is trying to convert the @FQTROQ variable into a DECIMAL.

You'd either need to do:

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

OR...

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

Answer 3

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

You need to cast the DECIMAL as a VARCHAR so that the output from the CASE statement have the same output value.