Create Proc proc_PageRecordset
@queryStr nvarchar(2000), --Query statement, used to obtain the requirements that must be met for the data to be displayed @keyField nvarchar (72), --Primary key
@pageSize int, --number of lines per page @pageNumber int, --What page
@filter varChar(2000)='', -Filter, where statements after
@order varChar(200)='' -Sorting method AS
BEGIN
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
DECLARE @sqlText_PageCount AS nvarchar(4000)
set @filter=replace(@filter,'#','''')
--This is the last bug found, because in the sorting, a must be determined with the same position as the table. A cannot be ranked second this time, and then 5th that time
if CharIndex(@keyField,@order)=0
Begin
Set @order=@order+','+@keyField
End
if (Rtrim(@filter)='')
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr + ' ' + @order
SET @sqlText_PageCount = 'Select Count(*) from (select ' +@queryStr+ ' ) as Table_temp'
end
else
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr + ' and ' + @filter + @order
SET @sqlText_PageCount = 'Select Count(*) AS MyCount from (select ' +@queryStr+ ' and ' + @filter +' ) as Table_temp'
end
--For example, if you want the 5th page data now, each page has 16 lines, then the data you want now is in the first 80 lines and is not in the original 64 lines.
SET @sqlText =
'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
'FROM (' + @sqlTable + ') AS tableA ' +
'WHERE ' + @keyField + ' NOT IN(SELECT TOP ' +
CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @keyField +
' FROM (' + @sqlTable + ') AS tableB)'
EXEC (@sqlText)
exec (@sqlText_PageCount)
END
GO