SQL Server (2005) and later versions are available. Corresponding methods written in the page logic to call the Stored Procedure
SQL SERVER (2005) and later versions are available. Corresponding methods written in the page logic to call the Stored Procedure
The 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 #)