Stored Procedure with Searching, Sorting and Pagination in SQL Server

Introduction

In this article, we will create a stored procedure in SQL Server that fetches data from a table and supports searching, sorting, and pagination based on the parameters passed to it.

Before starting the stored procedure, it helps to understand OFFSET and FETCH, which are used to implement pagination.

OFFSET

OFFSET is used to skip a set of rows from a table so you can start returning rows from a specific point.

You must use ORDER BY when using OFFSET.

SELECT column_names
FROM table_name
ORDER BY column_names
OFFSET [n] ROWS

The value of n must be an integer greater than or equal to 0.

FETCH

FETCH is used with ORDER BY and OFFSET to retrieve a limited number of rows after skipping the starting rows.

You must use OFFSET with FETCH, and FETCH must be greater than 0.

SELECT column_names
FROM table_name
ORDER BY column_names
OFFSET [n] ROWS
FETCH NEXT [n1] ROWS ONLY

If n is 10 and n1 is 5, the query skips the first 10 rows and returns the next 5 rows.

The example stored procedure operates on the Employees table, which contains 1000 rows in the article.

Employees table sample

Employees table sample 2

Stored Procedure

-- GET_EMPLOYEE_LIST 'E','first_name','DESC',0,20
CREATE OR ALTER PROCEDURE [dbo].[GET_EMPLOYEE_LIST]
    @SEARCH_TEXT AS VARCHAR(50) = '',
    @SORT_COLUMN_NAME AS VARCHAR(50) = '',
    @SORT_COLUMN_DIRECTION AS VARCHAR(50) = '',
    @START_INDEX AS INT = 0,
    @PAGE_SIZE AS INT = 10
AS
BEGIN
    DECLARE @QUERY AS VARCHAR(MAX) = '',
            @ORDER_QUERY AS VARCHAR(MAX) = '',
            @CONDITIONS AS VARCHAR(MAX) = '',
            @PAGINATION AS VARCHAR(MAX) = ''
 
    SET @QUERY = 'SELECT * FROM Employees '
 
    -- SEARCH OPERATION
    IF (ISNULL(@SEARCH_TEXT, '') <> '')
    BEGIN
        IF (ISDATE(@SEARCH_TEXT) = 1)
            SET @CONDITIONS = ' WHERE CAST(date_of_birth AS DATE) = CAST(' + @SEARCH_TEXT + ' AS DATE)'
        ELSE IF (ISNUMERIC(@SEARCH_TEXT) = 1)
        BEGIN
            SET @CONDITIONS = ' WHERE salary = ' + @SEARCH_TEXT + ' OR phone_number = CAST(' + @SEARCH_TEXT + ' AS VARCHAR(50))'
        END
        ELSE
        BEGIN
            SET @CONDITIONS = '
            WHERE
            first_name LIKE ''%' + @SEARCH_TEXT + '%''
            OR first_name +'' ''+last_name LIKE ''%' + @SEARCH_TEXT + '%''
            OR last_name LIKE ''%' + @SEARCH_TEXT + '%''
            OR email LIKE ''%' + @SEARCH_TEXT + '%''
            OR gender LIKE ''%' + @SEARCH_TEXT + '%''
            OR department LIKE ''%' + @SEARCH_TEXT + '%''
            OR phone_number LIKE ''%' + @SEARCH_TEXT + '%''
            '
        END
    END
 
    -- SORT OPERATION
    IF (ISNULL(@SORT_COLUMN_NAME, '') <> '' AND ISNULL(@SORT_COLUMN_DIRECTION, '') <> '')
    BEGIN
        SET @ORDER_QUERY = ' ORDER BY ' + @SORT_COLUMN_NAME + ' ' + @SORT_COLUMN_DIRECTION
    END
    ELSE
        SET @ORDER_QUERY = ' ORDER BY ID ASC'
 
    -- PAGINATION OPERATION
    IF (@PAGE_SIZE > 0)
    BEGIN
        SET @PAGINATION = ' OFFSET ' + CAST(@START_INDEX AS VARCHAR(10)) + ' ROWS
        FETCH NEXT ' + CAST(@PAGE_SIZE AS VARCHAR(10)) + ' ROWS ONLY'
    END
 
    IF (@CONDITIONS <> '') SET @QUERY += @CONDITIONS
    IF (@ORDER_QUERY <> '') SET @QUERY += @ORDER_QUERY
    IF (@PAGINATION <> '') SET @QUERY += @PAGINATION
 
    PRINT(@QUERY)
    EXEC(@QUERY)
END

Stored Procedure Parameters

The procedure accepts five parameters, each with a default value so the procedure can run without passing anything.

Search Text: value to search for in the records
Sort Column Name: column used for ordering the result
Sort Column Direction: ASC or DESC
Start Index: number of rows to skip
Page Size: number of rows to return

The body of the procedure builds the final SQL query from separate parts:

Query: base SELECT statement
Order Query: ORDER BY clause
Conditions: search conditions
Pagination: OFFSET and FETCH clause

Search Operation

If Search Text is not empty, the procedure searches in different ways based on the input type.

If the value is a date, it searches the date_of_birth column. If it is numeric, it searches salary and phone_number. Otherwise, it treats the input as plain text and searches across several string columns using LIKE.

-- SEARCH OPERATION
IF(ISNULL(@SEARCH_TEXT,'')<>'')
BEGIN
    IF(ISDATE(@SEARCH_TEXT)=1) SET @CONDITIONS=' WHERE CAST(date_of_birth AS DATE)=CAST('+@SEARCH_TEXT+'AS DATE)'
    ELSE IF(ISNUMERIC(@SEARCH_TEXT)=1)
    BEGIN
        SET @CONDITIONS=' WHERE salary='+@SEARCH_TEXT+' OR phone_number= CAST('+@SEARCH_TEXT+'AS VARCHAR(50))'
    END
    ELSE
    BEGIN
        SET @CONDITIONS='
        WHERE
        first_name LIKE ''%'+@SEARCH_TEXT+'%''
        OR first_name +'' ''+last_name LIKE ''%'+@SEARCH_TEXT+'%''
        OR last_name LIKE ''%'+@SEARCH_TEXT+'%''
        OR email LIKE ''%'+@SEARCH_TEXT+'%''
        OR gender LIKE ''%'+@SEARCH_TEXT+'%''
        OR department LIKE ''%'+@SEARCH_TEXT+'%''
        OR phone_number LIKE ''%'+@SEARCH_TEXT+'%''
        '
    END
END

Sort Operation

If sort column name and direction are provided, the procedure builds an ORDER BY clause. Otherwise, it defaults to ordering by ID ASC.

-- SORT OPERATION
IF(ISNULL(@SORT_COLUMN_NAME,'')<>'' AND ISNULL(@SORT_COLUMN_DIRECTION,'')<>'')
BEGIN
    SET @ORDER_QUERY=' ORDER BY '+@SORT_COLUMN_NAME+' '+@SORT_COLUMN_DIRECTION
END
ELSE SET @ORDER_QUERY=' ORDER BY ID ASC'

Pagination Operation

Pagination is applied only when Page Size is greater than zero. After building the individual parts, the procedure merges them into a single query and executes it.

-- PAGINATION OPERATION
IF(@PAGE_SIZE>0)
BEGIN
    SET @PAGINATION=' OFFSET '+(CAST(@START_INDEX AS varchar(10)))+' ROWS
    FETCH NEXT '+(CAST(@PAGE_SIZE AS varchar(10)))+' ROWS ONLY'
END
IF(@CONDITIONS<>'') SET @QUERY+=@CONDITIONS
IF(@ORDER_QUERY<>'') SET @QUERY+=@ORDER_QUERY
IF(@PAGINATION<>'') SET @QUERY+=@PAGINATION

Output

When the procedure runs with the given parameters, it returns the expected sorted and paginated data.

Stored procedure output

Conclusion

This stored procedure demonstrates one way to combine searching, sorting, and pagination in SQL Server using dynamic SQL with OFFSET and FETCH.