sql server CASE: error converting datatype varchar to float

Question

I am trying to add a column dynamically into my query.

I am running the following query:

DECLARE @detMethod varchar(20) = 'MAX(cov)';

WITH myTable as (
                  SELECT user1, (SELECT CASE @detMethod
                                           WHEN 'MAX(cov)' THEN MAX(cov)
                                           ELSE MAX(pcc*cov)
                                        END 
                                ) as method
                  FROM ....
                  WHERE ....
                )

/* some task on myTable */

It is giving me an error on this line:

SELECT user1, (SELECT CASE @detMethod

Error converting data type varchar to float.

One possible alternate is to use dynamic sql and store the whole query in a string, and then execute using sp_executesql. But what exactly is wrong with this code?

Edit: The code is fine, the error was further down in the query in the FROM clause where I had not enclosed @detMethod within the CASE block. The error line number it was giving me was the first line of the derived table.

ErikE · Accepted Answer · 2014-05-12 23:08:15Z

2

I see two possibilities:

Either one of the columns cov and pcc has a data type of varchar, and contains values that cannot be converted to float;
Or the error is further down in your query. Sometimes, the error line number given is not the line of the actual problem expression but the line of the start of the query. Since you're pointing out a line that is very close to the first line of the query, you could just be off by one (easy to do if you have a GO statement near the top of your file), and the error is NOT with this part of the query.

Your pattern of placing the CASE statement in a SELECT is unnecessary, but it's not what's causing the error. You can simplify like this:

WITH myTable AS (
   SELECT
      user1,
      method = -- Putting the alias first is clearer in my opinion
         CASE @detMethod
            WHEN 'MAX(cov)' THEN MAX(cov)
            ELSE MAX(pcc * cov)
         END
   FROM ...
   WHERE ...
)
...

Note that there is nothing wrong with using the alternate shorthand form of the CASE statement that you've chosen, which looks like this:

CASE {expression}
   WHEN {testvalue1} THEN {resultvalue1}
   WHEN {testvalue2} THEN {resultvalue2}
   ...
END

As long as you are testing for equality, then the full syntax is not required. If you use inequality or need to test for NULL or use a different expression in some cases, then obviously you have to use the expanded form:

CASE
   WHEN {expression} = {testvalue1} THEN {resultvalue1}
   WHEN {expression} = {testvalue2} THEN {resultvalue2}
   ...
END

But again, this shouldn't affect your situation.

Last, if your expression always uses Max, consider further simplifying like so:

MAX(
   CASE @detMethod
      WHEN 'MAX(cov)' THEN cov
      ELSE pcc * cov
   END
)

Or even (to better express intent to the next developer):

MAX(
   cov *
      CASE @detMethod
         WHEN 'MAX(cov)' THEN 1
         ELSE pcc
      END
)

edited May 12, 2014 at 23:08

answered May 12, 2014 at 22:49

ErikE

50.7k23 gold badges157 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Saad Over a year ago

cov and pcc both have data type float. They also contain values which are either float or null. When I execute the query using : SELECT user1, MAX(cov) it works perfectly fine.

ErikE Over a year ago

@user2989408 That doesn't make any sense. Why would a float column with a NULL value be converted to varchar when there is nothing to trigger such an implicit conversion?

ErikE Over a year ago

@SaadH Please see my update regarding the second possibility I see.

Saad Over a year ago

It's the same. The problem is not with null values because when I run MAX(cov) or MAX(pcc*cov) without CASE it works. I am also using @detMethod in the FROM clause of derived table.

Saad Over a year ago

got it, I enclosed @detMethod within the CASE blocks in the FROM clause too and now I have the result. Thankyou for your feedback.

|

Collectives™ on Stack Overflow

sql server CASE: error converting datatype varchar to float

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related