Error in ORDER BY clause using CASE WHEN
<p>I'm trying to allow ASC/DESC sort order to be defined by a parameter in a stored procedure.</p>

<p>After lots of research, I found this approach (with simplification):</p>

<pre><code>SELECT *
FROM MyTable
ORDER BY CASE WHEN @reverse = 1 THEN
              MyColumn
         END DESC,
         CASE WHEN @reverse = 0 THEN
              MyColumn
         END ASC
</code></pre>

<p>However, this code throws the following error:</p>

<blockquote>
  <p>Msg 408, Level 16, State 1, Line 8<br>
  A constant expression was encountered in the ORDER BY list, position 2.</p>
</blockquote>

<p>Why is this happening? Clearly MyColumn isn't a constant - it is a column name.</p>

<p>Using SQL Server 2016 in Compatibility Mode 2016 (130)</p>

<p>Thanks</p>