How to stored procedure and large table are dynamic order by performance

[Aldous]

I have a project work where there was a requirement that, there's a way using MSSQL to dynamically determine what column should be ordered by in a select, This is because the main motive behind creating this is to look into sorting tables by a column designated given some input, and when i search for that on the internet I found that there is no easy way to do this. Does anyone provide me simple way to achieve this.

Thanks

[Alejandro]

I think you need to choose the parameter to specify the sort type and also make use of case statement in the order by clause. Here i will provide you the simple coding about how do you need to implement it,

AS
BEGIN

SELECT
TableField.colmnA,
TableField.colmnB
FROM
TableField
ORDER BY
CASE
WHEN @SortType = 1 THEN FieldA
WHEN @SortType = 2 THEN FieldB
ELSE FieldA
END

END

[Aldous]

Your idea of paging the data is a very good one. The part that is bad is using variables for the table names, order by, etc. but I need to be able to change the asc/desc too. I guess that'd reduce it to 2 queries though, which is something, but it'd be a real mess with the number of fields it's on.

[RichiRich]

I think you need to use the INSERT INTO and ORDER BY methods to be used in your program to sort the items, and If sorting column is defferent from the PK, then SortColumn must be fetched as well, otherwise just the PK is necessary, and once this has been done then use the Join method to be used. This will combine all the data and sort the table content.