Use Stored procedures to implement database paging code and speed up page execution. You can test it.
Use Stored procedures to implement database paging code and speed up page execution. You can test it.
The code is as follows:
--*************************************** ****************
-- * Paging Stored Procedure *
-- * Sahara forest *
-- * 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, -- Page N
@ PageSize int -- number of lines per page
As
Set nocount on
Declare @ P1 int, -- P1 is the cursor id
@ Rowcount int
Exec sp_cursoropen @ P1 output, @ SqlStr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
Select ceiling (1.0 * @ rowcount/@ PageSize) as total number of pages --, @ rowcount as total number of rows, @ CurrentPage as current page
Set @ CurrentPage = (@ CurrentPage-1) * @ PageSize + 1
Exec sp_cursorfetch @ P1, 16, @ 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 (100) 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 the stored procedure
-- Exec P_Paging 'select * from Test_Students order by ID', 100,100 -- run
--
---- Delete Test table
-- If exists (select * from sysobjects where type = 'u' and name = n' Test _ Students ')
-- Drop table Test_Students
-- Go
