title: sql server CASE: error converting datatype varchar to float
body: <p>I am trying to add a column dynamically into my query.</p>
  
  <p>I am running the following query:</p>
  
  <pre><code>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 */
  </code></pre>
  
  <p>It is giving me an error on this line:</p>
  
  <pre><code>SELECT user1, (SELECT CASE @detMethod
  
  Error converting data type varchar to float.
  </code></pre>
  
  <p>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?</p>
  
  <p>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.   </p>