asp.net paging stored procedure parsing

The code is as follows

Copy Code

ALTER PROCEDURE [dbo]. [Sp_sql_paging] ( @SqlDataTable NVARCHAR (4000),--table name @PrimaryKey NVARCHAR (4000),--primary Key name @Fields NVARCHAR (4000),--the field to be returned @pageSize INT,--page size @pageIndex INT,--page number @recordCount INT OUTPUT,--Total number of records @strOrderBy NVARCHAR (4000),--sort @strWhere NVARCHAR (4000)--Query conditions ) As BEGIN SET NOCOUNT on DECLARE @strSQL1 NVARCHAR (4000)--SQL statement 1 DECLARE @strSQL2 NVARCHAR (4000)--SQL statement 2 DECLARE @strSQL3 NVARCHAR (4000)--SQL statement 3 SET @strSQL1 = ' SELECT ' + @PrimaryKey + ', row_number () over (' + @strOrderBy + ' As RowNumber from ' + @SqlDataTable + ' + @strWhere --Get the total number of records SET @strSQL3 = ' SELECT @recordCount = COUNT (*) from ' + @SqlDataTable + ' + @strWhere EXEC sp_executesql @stmt = @strSQL3, @params = N ' @recordCount as INT OUTPUT ', @recordCount = @recordCount OUTPUT --Paging query IF @pageIndex > @recordCount * 1.0/@pageSize + 1.0 OR @recordCount <= @pageSize BEGIN SET @pageIndex = 1 End SET @strSQL2 = ' SELECT ' + @Fields + ' from ' + @SqlDataTable + ' WHERE ' + @PrimaryKey + ' in (SELECT ' + @PrimaryKey + ' from (' + @strSQL1 + ') temptable WHERE RowNumber BETWEEN ' + str ((@pageIndex-1) * @pageSize + 1) + ' + ' + str (@pageIndex * @pageSize) + ') ' + @strOrderBy EXEC sp_executesql @strSQL2 End