Sql server Order by clause with a parameter value

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!

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 <> @MonthType THEN RLT.Value 
  END

 IF (@@ERROR <> 0)
  RETURN 1
 ELSE
  RETURN 0  

END

Answers


Do you want to order by RLT.Id when @LookupTypeId equals @MonthType and by RLT.Value otherwise? Then the case should be like this:

ORDER BY
CASE @LookupTypeId 
   WHEN @MonthType THEN RLT.Id
   ELSE RLT.Value
END

However, if RLT.Id and RLT.Value are not both the same type you will get a conversion error. If RLT.Id is int and RLT.Value is varchar then you could change the case to:

ORDER BY
CASE @LookupTypeId 
   WHEN @MonthType THEN STR(RLT.Id)
   ELSE RLT.Value
END

I think your order by clause syntax is mixed up - does this query run? or does it give a syntax error?

Is this what you are looking for?

ORDER BY  -- Order by will change for only Months by ID 
  CASE  @LookupTypeId  
    WHEN @MonthType THEN RLT.Id  
    ELSE RLT.Value  
  END

Need Your Help

JQuery Inline text shifts

html submit css

I have a submit button that on click, jquery checks if anything has been selected. If not then it does show() on an error message. Here's the html:

Label Text middle truncate

iphone

I have taken a custom tableViewCell with 2 labels .if the text in the second label exceeds i am showing the text in the second label with middle truncation. when the user clicks on the truncated text

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.