about using stored procedures to create a paging

Create | stored Procedures | pagination
2005-1-4
about using stored procedures to create a paging

Querying for SQL Server indexes today it is not intended to find a code at the end of an article about using stored procedures to create pagination, copying directly into the Query Analyzer wrong, finishing debugging a, move up to study together.

Note: The original author freedk the following as the author of this is a relatively optimal method in a large number of data sets outstanding performance

To create a WEB application, paging browsing is essential. This problem is a very common problem in database processing. The classic data paging method is: ADO records set pagination, that is, the use of ADO with the paging function (using cursors) to achieve pagination. However, this paging method applies only to situations where the data is small, because the cursor itself has a disadvantage: the cursor is stored in memory and is very time-consuming. Once the cursor is established, the associated record is locked until the cursor is canceled. Cursors provide a step-by-step scan of a particular set, typically using cursors to traverse data line by row, and to perform different operations depending on how the data is removed. For multiple tables and large tables, the cursor (large data set) loop is easy to get the program into a long wait or even crash.
More importantly, for a very large data model, it is a waste of resources to use the traditional method of loading the entire data source every time when paging is retrieved. The popular method of paging is generally to retrieve the chunk of the page size, rather than retrieving all the data, and then stepping through the current row.

Source of ideas:
Take the records from the Publish table to section N to article M:
SELECT Top M-n+1 *
From publish
WHERE (id not in
(SELECT top n-1 ID
from publish))

Stored procedures:

CREATE PROCEDURE Pagination3
@tblName varchar (255),--table name
@strGetFields varchar (1000) = ' * ',--columns to be returned
@fldName varchar (255) = ',--sorted field name
@PageSize int = 10,--page size
@PageIndex int = 1,--page number
@doCount bit = 0--Returns the total number of records, not 0 values.
@OrderType bit = 0,--set sort type, not 0 value descending
@strWhere varchar (1500) = '--Query criteria (note: Do not add where)
As

DECLARE @strSQL varchar (5000)--subject sentence
DECLARE @strTmp varchar (110)--Temporary variable
DECLARE @strOrder varchar (400)--Sort type

If @doCount!= 0
Begin
If @strWhere!= '
Set @strSQL = ' SELECT COUNT (*) total from where ' + @strWhere
Else
Set @strSQL = ' SELECT COUNT (*) Total from '
End
--The above code means that if @docount passes over 0, the total count is executed. All of the following code is @docount 0:

Else
Begin
If @OrderType!= 0
Begin
Set @strTmp = ' < (select Min '
Set @strOrder = ' ORDER by [' + @fldName + '] desc '
If @ordertype is not 0, it is important to perform descending order.

End
Else
Begin
Set @strTmp = ' > select Max '
Set @strOrder = ' ORDER by [' + @fldName + '] ASC '
End

If @PageIndex = 1
Begin
If @strWhere!= '

Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] where ' + @strWhere + ' + @st Rorder
Else

Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] ' + @strOrder
--If the first page executes the above code, this will speed up execution
End
Else
Begin
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from ['
+ @tblName + '] where [' + @fldName + '] ' + @strTmp + ' (['
+ @fldName + ']) from (select Top + str (@PageIndex-1) * @PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] ' + @strOrder + ') as Tbltmp '
+ @strOrder

If @strWhere!= '
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from ['
+ @tblName + '] where [' + @fldName + '] ' + @strTmp + ' (['
+ @fldName + ']) from (select Top + str (@PageIndex-1) * @PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + '
+ @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder
End
End
EXEC (@strSQL)
--print @strSQL
Go


The above code is debugged under WINXP_SP2 SQL server2000 Personal