title: Sql server Order by clause with a parameter value
body: <p>I have written following stored procedure. If I don’t use IF ELSE block Order by clause works fine but I need to use IF ELSE to build my select statement. Order by Is not working with IF else block. Can you please tell me what am I doing wrong?
  Thanks in Advance!</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[GetLookupItem] 
  
  -- #description Gets LookupItem 
  -- #tables   Subscriber.Reference.LookupItem,Subscriber.Reference.LookupText,Subscriber.Reference.LookupType
  -- #parameter:   @LookupTypeId       This parameter defines a type of lookup
  -- #parameter:   @LookupTextAbbreviationId            This Variable decides to tie Lookup Text to Abbreviation or Full Text. Most of the cases Full text will be used.
  -- #parameter:   @LanguageCode     To show the Lookup Text in this Language
  
   @LookupTypeId int,
   @LookupTextAbbreviationId int ,
   @LanguageCode varchar(5)
  
  
  AS
  
  BEGIN
  
   SET NOCOUNT ON;
   DECLARE @MonthType int
   SET @MonthType = (SELECT Id FROM Subscriber.Reference.LookupType WHERE Name = 'Months')
  
   IF  @LookupTextAbbreviationId IS NULL
    BEGIN
     SELECT RLI.Id,RLT.Value
     FROM Subscriber.Reference.LookupItem RLI
     INNER JOIN Subscriber.Reference.LookupText RLT
     ON RLI.LookupTextId = RLT.Id AND RLT.LanguageCode = @LanguageCode AND RLI.LookupTextAbbreviationId IS NULL   -- LookupTextAbbreviationId will be null for full text records in Subscriber.Reference.LookupItem
     WHERE RLI.LookupTypeId = @LookupTypeId 
    END
   ELSE
  
    SELECT RLI.Id,RLT.Value
    FROM Subscriber.Reference.LookupItem RLI
    INNER JOIN Subscriber.Reference.LookupText RLT
    ON RLI.LookupTextAbbreviationId = RLT.Id AND RLT.LanguageCode = @LanguageCode
    WHERE RLI.LookupTypeId = @LookupTypeId
  
  
          ORDER BY                -- Order by will change for only Months by ID
          CASE  @LookupTypeId 
     WHEN @MonthType THEN RLT.Id 
     END,
    CASE 
     WHEN @LookupTypeId &lt;&gt; @MonthType THEN RLT.Value 
    END
  
   IF (@@ERROR &lt;&gt; 0)
    RETURN 1
   ELSE
    RETURN 0  
  
  END
  </code></pre>