title: How to use Order By in a stored procedure without using dynamic SQL
body: <p>I've the following MS SQL stored procedure. I need to sort the results without using dynamic SQL and <code>sp_executesql</code> method</p>
  
  <p>@Order by can have the possible values <code>ProductName ASC</code>, <code>ProductName DESC</code>, <code>ProductCode ASC</code>, <code>VendorName DESC</code>, <code>VendorCode</code> or <code>ClientName</code></p>
  
  <p>I was trying to use <code>ORDER BY CASE</code>, is there any issue if the  <code>ProductName</code>, <code>ProductCode</code> are of different type?</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[SortedReport]
  (
      @ClientID INT,
      @RecordLimit,
      @FromDate DATETIME,
      @ToDate DATETIME,
      @OrderBy NVARCHAR(MAX)
  )
  
  AS
  BEGIN
  
  IF (@OrderBy IS NULL) BEGIN
      SET @OrderBy = 'ProductName';
  END    
  
  SELECT TOP (@RecordLimit) 
          sv.ClientID,
          sv.VendorID,
          sv.ProductID,
          sv.TransactionTime,
          sv.ClientName,
          sv.VendorName,
          sv.ProductName,
          sv.ProductCode,
          sv.VendorCode,
  FROM SortedReportiew AS sv 
  WHERE (sv.ClientID = @ClientID)
      AND (sv.TransactionTime &gt;= @FromDate)
      AND (sv.TransactionTime &lt; @Date)
  </code></pre>
  
  <p><strong>Update:</strong></p>
  
  <p>Is the below part correct? ref from <a href="http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicvsstatic2/623/" rel="noreferrer">here</a></p>
  
  <pre><code>ORDER BY 
      CASE @OrderBy WHEN 'ProductCode ASC' THEN ProductCode WHEN 'ProductCode DESC' THEN ProductCode END DESC,
      CASE @OrderBy WHEN 'ProductName ASC' THEN ProductName WHEN 'ProductName DESC' THEN ProductName END DESC,
  </code></pre>