Stored procedure that can handle pagination, sorting, and searching based on dynamic columns in Microsoft SQL Server:

Stored procedure that can handle pagination, sorting, and searching based on dynamic columns in Microsoft SQL Server:

CREATE PROCEDURE [dbo].[GetPagedData] ( @tableName NVARCHAR(100), @selectColumns NVARCHAR(MAX), @whereClause NVARCHAR(MAX), @orderByColumn NVARCHAR(100), @sortDirection NVARCHAR(10), @pageIndex INT, @pageSize INT ) AS BEGIN DECLARE @startIndex INT DECLARE @endIndex INT SET @startIndex = (@pageIndex - 1) * @pageSize + 1 SET @endIndex = @startIndex + @pageSize - 1 DECLARE @sql NVARCHAR(MAX) SET @sql = N' WITH CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@orderByColumn) + ' ' + @sortDirection + ') AS RowNum, ' + @selectColumns + ' FROM ' + QUOTENAME(@tableName) + ' WHERE ' + @whereClause + ' ) SELECT ' + @selectColumns + ' FROM CTE WHERE RowNum >= ' + CAST(@startIndex AS NVARCHAR(20)) + ' AND RowNum <= ' + CAST(@endIndex AS NVARCHAR(20)) EXEC sp_executesql @sql END 

Here's how you can use this stored procedure:

@tableName - The name of the table you want to query.@selectColumns - The list of columns you want to select, separated by commas.@whereClause - The WHERE clause you want to apply to filter the results.@orderByColumn - The name of the column you want to use to sort the results.@sortDirection - The direction you want to sort the results (ASC or DESC).@pageIndex - The index of the page you want to retrieve.@pageSize - The number of records you want to retrieve per page.

Here's an example of how you can call the stored procedure:

EXEC GetPagedData 'Customers', 'CustomerID, CompanyName, ContactName, City, Country', 'Country = ''USA''', 'CompanyName', 'ASC', 1, 10

This will return the first page of 10 customers from the USA, sorted by company name in ascending order.