Efficient MSSQL Stored procedure paging code (1/6)

In general small data paging is that we use a simple paging function on it, but if the millions data paging, then I have to consider the efficient MSSQL stored procedure paging code OH.
DECLARE @TotalCount int
DECLARE @TotalPageCount int
exec p_viewpage_a ' type1 ', ' * ', ' id ', ', ' ID ASC ', 1,0,4,3, @TotalCount output, @TotalPageCount output

SELECT * FROM Type1

Create PROC p_viewpage_a
/*
Nzperfect [No_miss] Efficient common paging stored procedure (bidirectional retrieval) 2007.5.7 qq:34813284
Warning: Tables or views that apply to a single primary key or existence of a unique value column
PS Tutorial: SQL statement is 8000 bytes, please note that the incoming parameter and the total SQL length do not exceed the specified range
*/
@TableName VARCHAR (200),--table name
@FieldList VARCHAR (2000),--Display column names, if all fields are *
@PrimaryKey VARCHAR (100),--single primary key or unique value key
@Where VARCHAR (2000),--the query condition does not contain a ' Where ' character, such as Id>10 and Len (userid) >9
@Order VARCHAR (1000),--sorting does not contain an ' order by ' character, such as ID Asc,userid desc, you must specify ASC or DESC
-Note that when the @sorttype=3 comes into effect, remember to add the primary key at the end, otherwise it will make you more depressed
@SortType INT,--collation 1: Positive order ASC 2: Reverse DESC 3: Multi-column Sorting method
@RecorderCount INT,--Total records 0: Returns the total record
@PageSize INT--number of records output per page
@PageIndex INT,--current page 111cn.net
@TotalCount INT OUTPUT,--Returns the total record
@TotalPageCount INT OUTPUT--Total number of pages returned
As
SET NOCOUNT on
IF ISNULL (@TotalCount, ') = ' SET @TotalCount = 0
SET @Order = RTRIM (LTRIM (@Order))
SET @PrimaryKey = RTRIM (LTRIM (@PrimaryKey))
SET @FieldList = REPLACE (RTRIM (LTRIM (@FieldList)), ', '
While CHARINDEX (', ', @Order) > 0 OR CHARINDEX (', ', @Order) > 0
BEGIN
SET @Order = REPLACE (@Order, ', ', ', ', ')
SET @Order = REPLACE (@Order, ', ', ', ', ')
End
IF ISNULL (@TableName, ') = ' OR ISNULL (@FieldList, ') = '
or ISNULL (@PrimaryKey, ') = '
or @SortType < 1 or @SortType >3
or @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
BEGIN
PRINT (' err_00 ')
Return
End

Home 1 2 3 4 5 6 last