Sortable MVC Tables with Stored Procedure

Question

Hello everyone and thanks for the help in advance. I am developing a MVC application that requires a sortable table. The issue is that the tables utilize queries that are best suited for stored procedures. While researching, I cam across older articles such as: https://stackoverflow.com/questions/15621609/t-sql-conditional-order-by, using either dynamic SQL or some type of CASE structure, but wondered if there were better ways to approach this problem. New example seem to rely on creating conditional LINQ queries within the controller, but I don't think that would be optimal. Any help would be appreciated.

Answer 1

T-SQL has OFFEST and FETCH NEXT

SELECT SomeColumns 
FROM MyTable
WHERE aColumn = 'aValue'
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Answer 2

using either dynamic SQL or some type of CASE structure

Yes, these are typically the solution. The solution with CASE breaks down quite quickly if you want to sort by more than one column, since you need keep different data types in different CASE expressions.

The other alternative is to sort in the client.