sqlserver2000 stored procedure paging-only one table

Search from the Internet, the use of cursors for the stored procedure paging, but there is a problem, there are two tables, the first table is empty, and the second table is the real data.

The stored procedure is now modified so that it only generates a single table.

CREATE PROCEDURE [dbo]. [Pr_querybypage]
@sqlstr nvarchar (4000),--Query SQL
@currentpage int,--page record number
@pagesize int,--Show records per page
@tablename varchar (100)--table name
As
SET NOCOUNT ON
declare @P1 int,--P1 is the ID of the cursor
@rowcount int,--
@sql varchar (100)--Dynamic SQL statement
--a stupid method that uses a temporary table to receive an empty result set
Set @sql = ' SELECT top 0 * into # #tmpTable from ' [email protected]
EXEC (@sql)
INSERT INTO # #tmpTable exec sp_cursoropen @P1 output, @sqlstr, @scrollopt =1, @ccopt =1,@[email protected] Output
--select Ceiling (1.0* @rowcount/@pagesize) as Totalpage, @rowcount as [rowcount]
Set @currentpage = (@currentpage-1) * @pagesize +1
exec sp_cursorfetch @P1, @currentpage, @pagesize
EXEC sp_cursorclose @P1
Drop Table # #tmpTable
SET NOCOUNT OFF
GO

Pass in 4 parameters, SQL statement, page number, data per page, table name.

The following SQL statements can be used in Query Analyzer:

EXEC pr_querybypage ' select * FROM table1 ORDER by id ', 1,10, ' table1 '

C # calls:

To perform a paged stored procedure, note that status= ' 0 ' is a front and back two single quotes, not double quotes.
String sql = "Exec pr_querybypage ' select * FROM table1 a where status= ' 0";
if (Baodanhao! = "")
{
SQL + = "and A.baodanhao like"% "+ Baodanhao +"% "";
}
SQL + = "ORDER by id", "+page+", "+pagesize+", ' table1 ' ";
Oledbhelper = new Oledbhelper ();
return oledbhelper.executequeryfy (SQL);

sqlserver2000 stored procedure paging-only one table