Stored procedure paging

I haven't been blog for a long time, write a method of returning data based on the Zou Jian's paging stored procedure

Proc Code:

/ * SQL CODE 2004-08-27 * / create procedure dbo.P_splitpage

@SQL NVARCHAR (4000), - To execute the SQL statement @currentpage int, - the page number @PageSize Int, - the size of each page @Recordcount Int = 0 out, - total record @PageCount Int = 0 OUT - Total number AS

Set NoCount on Declare @ P1 Int

EXEC SP_CURSoropen @ p1 output, @ SQL, @ scrollopt = 1, @ ccopt = 1, @ rowcount = @ PageCount Output

SELECT @ RecordCount = @ PageCount, @ PageCount = ceiling (1.0 * @ PageCount / @ PageSize), @ CurrentPage = (@ CurrentPage-1) * @ PageSize 1 SELECT @RecordCount RecordCount, @ PageCount PageCount, @ CurrentPage CurrentPage EXEC SP_Cursorfetch @ P1, 16, @ CurrentPage, @ Pagesize Exec sp_cursorclose @ p1go

Create a table, such as:

Accounts

{ID, Accountname, Fullname, Password ....

Query process SQL statement:

SELECT * ACCOUNTS

Create this statement into a stored procedure:

/ * Proc code 2004-08-27 * / create procedure dbo.p_accounts_select @intpage int, @intpagesize int, @intpageamount int out @intpageamount int outas

Exec P_SPLITPAGE 'SELECT * ACCUNTS', @ INTPAGE, @ INTPAGESIZE, @ INTRECORDCOUNT OUT, @ INTPAGEAMOUNT OUTGO

Through "query analyzer"

Declare @PageAmount Int, @ recordcount intexec p_accounts_select 2, 1, @ pageamount out, @ recordcount out

Will find that the return result set is three, the first result set is empty, the second result set is the total number of data records returned, the number of page numbers cannot be obtained at the same time, but it is not possible to get The record set and the number of page numbers are done twice, using C # DataReader.nextResult can be implemented. The method is as follows:

///