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.

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