Stored procedure in SQL that supports filtering, pagination, and sorting

 Creating a stored procedure in SQL that supports filtering, pagination, and sorting involves several steps.

Assuming you have a table called `YourTable`, and you want to create a stored procedure that supports filtering on a specific column, pagination, and sorting based on another column:

-- Create the stored procedure

CREATE PROCEDURE GetFilteredData

    @FilterColumn VARCHAR(255),

    @FilterValue VARCHAR(255),

    @SortColumn VARCHAR(255),

    @SortDirection VARCHAR(4),

    @PageNumber INT,

    @PageSize INT

AS

BEGIN

    -- Calculate the offset for pagination

    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;

    -- Dynamic SQL to build the query based on parameters

    DECLARE @SqlQuery NVARCHAR(MAX);

    SET @SqlQuery = '

        SELECT *

        FROM YourTable

        WHERE 1 = 1'; -- Always true condition to build upon

    -- Add filtering condition if provided

    IF @FilterColumn IS NOT NULL AND @FilterValue IS NOT NULL

    BEGIN

        SET @SqlQuery = @SqlQuery + ' AND ' + @FilterColumn + ' = @FilterValue';

    END

    -- Add sorting

    SET @SqlQuery = @SqlQuery + '

        ORDER BY ' + @SortColumn + ' ' + @SortDirection;

    -- Add pagination

    SET @SqlQuery = @SqlQuery + '

        OFFSET ' + CAST(@Offset AS VARCHAR(10)) + ' ROWS

        FETCH NEXT ' + CAST(@PageSize AS VARCHAR(10)) + ' ROWS ONLY;';

    -- Execute the dynamic SQL

    EXEC sp_executesql @SqlQuery, N'@FilterValue VARCHAR(255)', @FilterValue;

END;

Explanation:

1. The stored procedure `GetFilteredData` accepts parameters for filtering (`@FilterColumn` and `@FilterValue`), sorting (`@SortColumn` and `@SortDirection`), pagination (`@PageNumber` and `@PageSize`).

2. It calculates the offset for pagination based on the page number and page size.

3. The dynamic SQL `@SqlQuery` is used to build the query based on the provided parameters.

4. Filtering conditions are added to the query if `@FilterColumn` and `@FilterValue` are provided.

5. Sorting is applied based on `@SortColumn` and `@SortDirection`.

6. Pagination is implemented using the `OFFSET` and `FETCH NEXT` clauses.

7. The dynamic SQL is executed using `sp_executesql`.

You can call this stored procedure with the appropriate parameters to retrieve filtered, sorted, and paginated data from your table.