Dynamic order direction

by

You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make one or other of them always equal a single value:

ORDER BY
      CASE WHEN @OrderDirection = 0 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END ASC,
      CASE WHEN @OrderDirection = 1 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy           
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END DESC

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. How do I perform an IF…THEN in an SQL SELECT?
  3. Can I use CASE statement in a JOIN condition?
  4. SQL Server Insert if not exists
  5. “CASE” statement within “WHERE” clause in SQL Server 2008
  6. SQL Server – How to lock a table until a stored procedure finishes
  7. When no ‘Order by’ is specified, what order does a query choose for your record set?
  8. Stored procedure or function expects parameter which is not supplied
  9. What is a stored procedure?
  10. OR is not supported with CASE Statement in SQL Server
  11. Search text in stored procedure in SQL Server
  12. SQL Call Stored Procedure for each Row without using a cursor
  13. SQL Server SELECT into existing table
  14. How do I execute a stored procedure once for each row returned by query?
  15. Case in Select Statement
  16. SQL: Select dynamic column name based on variable
  17. How to update and order by using ms sql
  18. How to return the output of stored procedure into a variable in sql server
  19. How do I pass a list as a parameter in a stored procedure?
  20. Why execute stored procedures is faster than SQL query from a script?
  21. “Order By” using a parameter for the column name
  22. Custom Date/Time formatting in SQL Server
  23. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. SELECT query with CASE condition and SUM()
  26. How to pass a temp table as a parameter into a separate stored procedure
  27. Eliminate and reduce overlapping date ranges
  28. How to execute a stored procedure inside a select query
  29. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  30. Call stored procedure with table-valued parameter from java

Leave a Comment