Categories
Uncategorized

Sql Script to implement Server side Paging and Sorting

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.

[sourcecode language=”sql”]

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
[/sourcecode]