I myself, am not much of an SQL guy, so when I was asked up to write a stored procedure to handle server side sorting and paging, I was pretty skeptical about it.
So here is what I came up with, Hope this help you too.
I have 4 input fields to it. These fields are as follows
1. sort_order : to pass wether the order is “Ascending” or “Descending”.
2. sort_column : Here I pass the column name using which the table will be sorted.
3. page_number : Used to specify the current page number and
4. page_size : Is used to specify the amount of rows allowed per page.
also I have setup some logic to skip and take rows depending upon which page the user is on and the page size
@rows_to_skip = (@page_number – 1) * @page_size
Below is the full SQL script.
USE [my-db] ALTER PROCEDURE [dbo].[SP_Get_Records] @record_id INT, @sort_order varchar(50), @sort_column varchar(50), @page_number INT = 1, @page_size INT = 5, AS BEGIN DECLARE @rows_to_skip INT SET @rows_to_skip = (@page_number - 1) * @page_size SELECT * FROM ( SELECT *, row_no = ROW_NUMBER() OVER (ORDER BY CASE WHEN @sort_column = 'first_name' AND @sort_order = 'asc' THEN dbo.Users.FirstName END ASC, CASE WHEN @sort_column = 'first_name' AND @sort_order = 'desc' THEN dbo.Users.FirstName END DESC, CASE WHEN @sort_column = 'last_name' AND @sort_order = 'asc' THEN dbo.Users.LastName END ASC, CASE WHEN @sort_column = 'last_name' AND @sort_order = 'desc' THEN dbo.Users.LastName END DESC, CASE WHEN @sort_column = 'NULL' AND @sort_order = 'asc' THEN dbo.Users.FirstName END ASC) FROM dbo.Users WHERE record_id = @record_id ) p WHERE row_no >= @rows_to_skip + 1 and row_no <= @rows_to_skip + @page_size END GO