If you are developing some applications with .NET Framework, you should familiar with some built-in sorting and paging methods of some user controls(for example, grids…) in both web and Windows enviroments for your data which are stored in SQL Server. But to be honest, I do not like these methods so much. In some cases, they really rock and save life, but in some cases you really have to think about a better way for sorting ang paging.
Nowadays, I am in a case that I have to write some custom sorting and paging methods for my applications. The reason is because of some performance needs and also to have more control on these methods. So I gave these sorting and paging duty to SQL Server and create a simple pattern as stored procedure.
With this pattern, I am able to sort my data according to any column and also apply the paging according to this sort with preferred page size. And as you know that this is not so easy with some built-in control methods…
So here you can use this pattern. And if you have a better approach or idea, please let me know…
ALTER PROCEDURE SEARCH_SOMETHING
(
@param1 nvarchar(50),
@param2 int,
...
..
.
@skipedIndex int,
@pagesize int,
@sortColumn nvarchar(50),
@direction nvarchar(1)
)
AS
SELECT * FROM
(
Select
Table1.Column1,
Table1.Column2,
Table1.Column3,
row_number() OVER (
ORDER BY
CASE
WHEN @sortColumn='' AND @direction='' THEN Table1.Column1
END DESC,
CASE -----D for desc
WHEN @sortColumn='Column1' AND @direction='D' THEN Table1.Column1
END DESC,
CASE ----A for asc
WHEN @sortColumn='Column1' AND @direction='A' THEN Table1.Column1
END ASC,
CASE
WHEN @sortColumn='Column2' AND @direction='D' THEN Table1.Column2
END DESC,
CASE
WHEN @sortColumn='Column2' AND @direction='A' THEN Table1.Column2
END ASC
) AS rownumber
from Table1
WHERE ---THESE ARE THE CONDITIONS FOR YOUR SQL QUERY
(@param1='' or Table1.Column1 = @param1) AND
(@param2=-1 or Table1.Column2 = @param2) AND
......
..
..
) as t ---THIS IS THE PAGING CONDITIONS
WHERE (
(@skipedIndex=-1 and @pagesize=-1 ) OR
(t.rownumber between @skipedIndex+1 and @skipedIndex + @pageSize)
)
