I have a stored procedure that retrieves data by joining multiple tables but there's a .net assembly which has a detailed logic which provides sorting of the records retrieved through the stored proc. Since I need pagination to display the records on UI, I had to first call the proc and get "all" the records from the proc and then call the .net assembly to get sorting of the records and then apply pagination on top of the sorted records in memory which is not the most optimal solution as all the records every time for every single page.
I was contemplating on using SQLCLR to call the .net assembly method through a UDF or SP and call that UDF/SP from my GET proc.
Is this a best solution for this kind of a problem or are there any alternatives?
Versions - SQL Server 2016 and .Net Framework 4.5
EDIT: @Ben Thul - The records returned by the stored procedure are based on the how they are saved in the persistence layer. There are some data points that needs to be analyzed for each item in the result set returned by the stored proc and based on the values of those data points, the records (which might otherwise feature up top) might get a lower priority ranking. This rules engine is written as a .net assembly. And since this is more of a business logic, ideally this is not a good contender of being part of a stored procedure. So, once I get those records back, this rules engine has to be called to get the priority ranking of each item in the result set and that's what becomes the default sorting criteria when the grid is loaded.