Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Conditional SORT BY

Posted on 2006-11-03
Medium Priority
Last Modified: 2008-02-26
I am trying to run a SELECT query from within a Stored Procedure and I need to change how I sort the SELECT depending on a variable that is passed into the SP.  Of the three fields that the SELECT can be sorted by, one is datetime, one is int, and one is nvarchar(255).  I have looked at several examples but none of them ever seem to fully work for me.  All the examples used the CASE statement.  
I have tried grouping all three WHENs under one CASE statement.  This compiles OK but when I actually run the SP it works OK for the int field and the datetime field but I get a data type conversion error on the nvarchar field.
Ive tried using three seperate CASE statements, one for each field.  I get a syntax error when I try to compile.
Ive tried nesting three CASE statements and again this compiles and runs OK on the int and datetime fields but gets a data type conversion error when sorted on the nvarchar field.
Also, I need to be able to specifiy how the sort is done, ASC v. DESC.  If I try to put ASC or DESC on the same line as the WHEN and field name I get a syntax error when I try to compile.  If I move the ASC or DESC after the END keyword, which has been suggested on a couple of examples Ive seen, I get syntax errors on the CASE statement when I try to compile.
Here's what is the most successful so far:

SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
      CASE @Sort
            WHEN 1 THEN empLastName
            WHEN 2 THEN chkLockedFor
            WHEN 3 THEN chkLockedDate

This compiles and runs OK for @Sort = 2 & 3 but gives this error when @Sort = 1:

Syntax error converting datetime from character string.

Any ideas would be appreciated.
Question by:Wilbat
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 17867209
you have to split like this, to avoid data type conversion issues:
SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
,CASE WHEN @Sort = 2 THEN chkLockedFor ELSE NULL END
,CASE WHEN @Sort = 3 THEN chkLockedDate ELSE NULL END

LVL 23

Expert Comment

ID: 17867238
Hi Wilbat,

All 3 fields that may be ordered by must be of the same datatype, so you should convert the chkLockedFor and chkLockedDate fields to varchars:

SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
     CASE @Sort
          WHEN 1 THEN empLastName
          WHEN 2 THEN cast(chkLockedFor as varchar)
          WHEN 3 THEN cast(chkLockedDate as varchar)


Author Comment

ID: 17867321
that works well, but is there a way to specify ASC or DESC?

that wont work because it kills the ability to sort correctly on the datetime field, thanks though.

Author Comment

ID: 17867334
nevermind angelIII, I figured it out.
kudos, you're the big winner!

Author Comment

ID: 17867350
for anyone that may want to know how I did the sorting, here it is:

SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
      CASE WHEN @Sort = 1 THEN empLastName ELSE NULL END DESC,
      CASE WHEN @Sort = 2 THEN chkLockedFor ELSE NULL END DESC,
      CASE WHEN @Sort = 3 THEN chkLockedDate ELSE NULL END ASC

just put the DESC or ASC after END.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question