• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Conditional SORT BY

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)
ORDER BY
      CASE @Sort
            WHEN 1 THEN empLastName
            WHEN 2 THEN chkLockedFor
            WHEN 3 THEN chkLockedDate
      END

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.
0
Wilbat
Asked:
Wilbat
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to split like this, to avoid data type conversion issues:
SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
ORDER BY CASE WHEN @Sort = 1 THEN empLastName ELSE NULL END
,CASE WHEN @Sort = 2 THEN chkLockedFor ELSE NULL END
,CASE WHEN @Sort = 3 THEN chkLockedDate ELSE NULL END

0
 
adatheladCommented:
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)
ORDER BY
     CASE @Sort
          WHEN 1 THEN empLastName
          WHEN 2 THEN cast(chkLockedFor as varchar)
          WHEN 3 THEN cast(chkLockedDate as varchar)
     END



0
 
WilbatAuthor Commented:
angelIII
that works well, but is there a way to specify ASC or DESC?

adathelad
that wont work because it kills the ability to sort correctly on the datetime field, thanks though.
0
 
WilbatAuthor Commented:
nevermind angelIII, I figured it out.
kudos, you're the big winner!
0
 
WilbatAuthor Commented:
for anyone that may want to know how I did the sorting, here it is:

SELECT * FROM #TemporaryTable WHERE (RowID >= @Start) AND (RowID <= @End)
ORDER BY
      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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now