Paging Stored Procedures

Stored Procedures | paging

Test the next 1.2 million data, query less than 1 minutes, and using the traditional query method, will take several minutes

/* Paging Stored procedure
Descript: Paging Stored procedures
Author:Blue.Dream
Date:2004-8-18 21:01
*/
CREATE PROCEDURE Listpage (
@tblName nvarchar,----a connection to a table or tables to display
@fldName nvarchar = ' * ',----list of fields to display
@pageSize int = Ten,----the number of records displayed per page
@page int = 1,----to display the record for that page
@pageCount int = 1 OUTPUT,----Total number of pages after the query results are paginated
@Counts int = 1 OUTPUT,----number of records queried
@ID nvarchar,----primary key for the primary table
@fldSort nvarchar = null,----sort field list or condition
@Sort bit = 0,----Sort method, 0 is ascending, 1 is descending
@strCondition nvarchar = null----query condition, no where
)
As
SET NOCOUNT on
Declare @sqlTmp nvarchar (1000)----store dynamically generated SQL statements
Declare @strTmp nvarchar (1000)----A query that holds the total number of query results
Declare @strID nvarchar (1000)----A query that holds the ID at the beginning or end of a query
Declare @sqlSort nvarchar----sort criteria for storing temporary builds
Declare @intCounts int----Number of records to move
Declare @BeginID int----start with ID
Declare @EndID int----end of ID

--------first generate a sort method---------
If @Sort = 0--Ascending
Begin
If not (@fldSort is null)
Set @sqlSort = ' ORDER BY ' + @fldSort
Else
Set @sqlSort = ' ORDER BY ' + @ID
End
Else--Descending
Begin
If not (@fldSort is null)
Set @sqlSort = ' ORDER BY ' + @fldSort + ' DESC '
Else
Set @sqlSort = ' ORDER BY ' + @ID + ' DESC '
End

--------Generate query Statements--------
--Here @strtmp statements to get the number of query results
If @strCondition is null--no display condition set
Begin
Set @sqlTmp = @fldName + ' from ' + @tblName
Set @strTmp = ' Select @Counts =count (' + @ID + ') from ' + @tblName
Set @strID = ' from ' + @tblName
End
Else
Begin
Set @sqlTmp = + @fldName + ' from ' + @tblName + ' where ' + @strCondition
Set @strTmp = ' Select @Counts =count (' + @ID + ') from ' + @tblName + ' where ' + @strCondition
Set @strID = ' from ' + @tblName + ' where ' + @strCondition
End

----The total number of results obtained from the query-----
EXEC sp_executesql @strTmp, N ' @Counts int out ', @Counts out

--Get Total paging
If @Counts <= @pageSize
Set @pageCount = 1
Else
Set @pageCount = (@Counts/@pageSize) + 1

--Calculates the number of records to move
If @page = 1
Set @intCounts = @pageSize
Else
Begin
Set @intCounts = (@page-1) * @pageSize + 1
End

The ID of the first record of this page after the page is-----obtained
Set @strID = ' Select @BeginID = ' + @ID + ' + @strID

Set @intCounts = @intCounts-@pageSize + 1
SET ROWCOUNT @intCounts
EXEC sp_executesql @strID, N ' @BeginID int out ', @BeginID out

The ID of the last record in this page after the page is-----obtained
Set @intCounts = @intCounts + @pageSize-1
Print @intCounts
SET ROWCOUNT @intCounts
EXEC sp_executesql @strID, N ' @BeginID int out ', @EndID out

------Restore System Settings-----
SET ROWCOUNT 0
SET NOCOUNT on

------Return Query Results-----
If @strCondition is null
Set @strTmp = ' SELECT ' + @sqlTmp + ' where ' + @ID + ' between ' + str (@BeginID) + ' and ' + str (@EndID)
Else
Set @strTmp = ' SELECT ' + @sqlTmp + ' where ' + @ID + ' (between ' + str (@BeginID) + ' and ' + str (@EndID) + ') and ' + @s Trcondition

If not (@sqlSort is null)
Set @strTmp = @strTmp + @sqlSort
EXEC sp_executesql @strTmp
Go