Dynamic Sorting within SQL Stored Procedures

by

Yeah, it’s a pain, and the way you’re doing it looks similar to what I do:

order by
case when @SortExpr="CustomerName" and @SortDir="ASC" 
    then CustomerName end asc, 
case when @SortExpr="CustomerName" and @SortDir="DESC" 
    then CustomerName end desc,
...

This, to me, is still much better than building dynamic SQL from code, which turns into a scalability and maintenance nightmare for DBAs.

What I do from code is refactor the paging and sorting so I at least don’t have a lot of repetition there with populating values for @SortExpr and @SortDir.

As far as the SQL is concerned, keep the design and formatting the same between different stored procedures, so it’s at least neat and recognizable when you go in to make changes.

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Function vs. Stored Procedure in SQL Server
  3. SQL Server – How to lock a table until a stored procedure finishes
  4. What is a stored procedure?
  5. How to assign an exec result to a sql variable?
  6. SQL Server SELECT into existing table
  7. How to check if a stored procedure exists before creating it
  8. How can I determine the status of a job?
  9. Procedure or function !!! has too many arguments specified
  10. How can I generate a temporary table filled with dates in SQL Server 2000?
  11. Stored procedure that exports data into csv files only exports to one file
  12. In SQL Server, what does “SET ANSI_NULLS ON” mean?
  13. Comma separated values in a database field
  14. Inserting rows into a table with one IDENTITY column only [duplicate]
  15. How to get Return Value of a Stored Procedure
  16. Eliminate and reduce overlapping date ranges
  17. Simple way to transpose columns and rows in SQL?
  18. Only inserting a row if it’s not already there
  19. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  20. Most efficient way in SQL Server to get date from date+time?
  21. Convert varchar into datetime in SQL Server
  22. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  23. How do I group on continuous ranges
  24. join comma delimited data column
  25. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  26. How do I pass a list as a parameter in a stored procedure?
  27. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  28. SQL – Call Stored Procedure for each record
  29. Group consecutive rows of same value using time spans
  30. Previous Monday & previous Sunday’s date based on today’s date

Leave a Comment