SQLSERVER Pager store procedure paging Stored procedure

Copy codeThe Code is as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo]. [Pagination]
@ Page int = 1, -- current Page number
@ PageSize int = 10, -- number of records per page (page size)
@ Table nvarchar (500), -- Table name or view name, or even nested SQL: (Select * From Tab Where ID> 1000) Tab
@ Field nvarchar (800) = '*', -- return the Field name of the record set, separated by ",". The default value is "*"
@ OrderBy nvarchar (100) = 'id ASC ', -- sorting rule
@ Filter nvarchar (500), -- Filter Condition
@ MaxPage smallint output, -- execution result-1 error, 0 false, maxpage true
@ TotalRow int output, -- total number of records/* 22:11:00 update */
@ Descript varchar (100) output -- result description
AS
BEGIN

-- ===================================================== ======
-- Author: Jimmy. Yu
-- Create date: 2007-5-11
-- Description: General paging stored procedures for SQL 2005 and later versions
-- ===================================================== ======

Set ROWCOUNT @ PageSize;

Set @ Descript = 'successful ';
----------------- Parameter detection ----------------
If len (RTRIM (LTRIM (@ Table)!> 0
Begin
Set @ MaxPage = 0;
Set @ Descript = 'table name is empty ';
Return;
End

If len (RTRIM (LTRIM (@ OrderBy)!> 0
Begin
Set @ MaxPage = 0;
Set @ Descript = 'order is empty ';
Return;
End

If isnull (@ PageSize, 0) <= 0
Begin
Set @ MaxPage = 0;
Set @ Descript = 'page size error ';
Return;
End

If isnull (@ Page, 0) <= 0
Begin
Set @ MaxPage = 0;
Set @ Descript = 'page error ';
Return;
End
------------------- Detection ended ----------------

Begin Try
-- Integrated SQL
Declare @ SQL nvarchar (4000), @ Portion nvarchar (4000 );

Set @ Portion = 'row_number () OVER (order by '+ @ OrderBy +') as rownum from '+ @ Table;

Set @ Portion = @ Portion + (case when len (@ Filter)> = 1 THEN ('where' + @ Filter + ') AS tab') ELSE (') AS tab ') END );

Set @ SQL = 'select TOP ('+ CAST (@ PageSize AS nvarchar (8) +') '+ @ Field +' FROM (Select '+ @ Field + ', '+ @ Portion;

Set @ SQL = @ SQL + 'where tab. ROWNUM> '+ CAST (@ Page-1) * @ PageSize AS nvarchar (8 ));

-- Execute SQL to retrieve the record set on the current page
Execute (@ SQL );
--------------------------------------------------------------------

-- Integrated SQL
Set @ SQL = 'set @ Rows = (Select MAX (ROWNUM) FROM (Select '+ @ Portion + ')';

-- Execute the SQL statement and obtain the maximum page number.
Execute sp_executesql @ SQL, n' @ Rows int output', @ TotalRow output;
Set @ MaxPage = (case when (@ TotalRow % @ PageSize) <> 0 THEN (@ TotalRow/@ PageSize + 1) ELSE (@ TotalRow/@ PageSize) END );
End Try
Begin Catch
-- Capture errors
Set @ MaxPage =-1;
Set @ Descript = 'error line: '+ CAST (ERROR_LINE () AS varchar (8) +', error number: '+ CAST (ERROR_NUMBER () AS varchar (8 )) + ', error message:' + ERROR_MESSAGE ();
Return;
End Catch;

-- Execution successful
Return;
END

Corresponding Method for calling the stored procedure written in the page logic (C #)