Copy Code code 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 record bars 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) = ' * ',--Returns the recordset field name, "," is separated by the default is "*"
@OrderBy nvarchar = ' ID ASC ',--collation
@Filter nvarchar (500),--Filtration conditions
@MaxPage smallint output,--execution result-1 error, 0 false, Maxpage true
@TotalRow int output,--Total Records/* 2007-07-12 22:11:00 Update * *
@Descript varchar OUTPUT--Result description
As
BEGIN
-- =============================================
--Author:Jimmy.Yu
--Create date:2007-5-11
--Description:sql more than 2005 version of common paging stored procedures
-- =============================================
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
-------------------End of Detection----------------
Begin Try
--Consolidating 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, take the current page recordset
Execute (@SQL);
--------------------------------------------------------------------
--Consolidating SQL
Set @SQL = ' Set @Rows = (select MAX (rownum) from (SELECT ' + @Portion + ') ';
--Execute SQL, take maximum page number
Execute sp_executesql @SQL, N ' @Rows int output ', @TotalRow output;
Set @MaxPage = (@TotalRow% @PageSize) <>0 THEN (@TotalRow/@PageSize + 1) ELSE (@TotalRow/@PageSize) E ND);
End Try
Begin Catch
--Catching 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;
--Successful execution
return;
End
The corresponding method of calling the stored procedure (C #) that is written in the appropriate page logic
