Paging stored procedures (implementing a database's paging code with stored procedures)

Copy CodeThe code is as follows:
--*******************************************************
--* Paging Stored Procedure *
--* the Great Forest of Sahara
--* 2010-6-28 *
--*******************************************************

if exists (select * from sysobjects where type= ' P ' and Name=n ' p_paging ')
drop procedure P_paging
Go

CREATE PROCEDURE P_paging
@SqlStr nvarchar (4000),--query string
@CurrentPage int,--nth page
@PageSize INT--Number of rows per page
As
SET NOCOUNT ON
declare @P1 int,--P1 is the ID of the cursor
@rowcount int
EXEC sp_cursoropen @P1 output, @SqlStr, @scrollopt =1, @ccopt =1, @rowcount = @rowcount output
Select Ceiling (1.0* @rowcount/@PageSize) as total number of pages--, @rowcount as rows, @CurrentPage as current page
Set @CurrentPage = (@CurrentPage-1) * @PageSize +1
exec sp_cursorfetch @P1, @CurrentPage, @PageSize
EXEC sp_cursorclose @P1
SET NOCOUNT OFF
Go


----Create a test table
--IF exists (SELECT * from sysobjects where type= ' U ' and Name=n ' test_students ')
--drop table test_students
--go
--create Table Test_students (
--ID int IDENTITY (1,1) NOT NULL,
--name nvarchar NOT NULL
--)
--
----Create test data
--declare @i int
--set @i = 100000
--while @i>0
--Begin
--INSERT into test_students values (' name ')
--Set @i = @i-1
--End
--
----Execute Stored Procedures
--exec p_paging ' SELECT * from test_students ORDER by id ', 100,100--Execute
--
----Delete a test table
--IF exists (SELECT * from sysobjects where type= ' U ' and Name=n ' test_students ')
--drop table test_students
--go