Stored Procedure Paging __ Storage

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: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 number

@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

IF @SortType = 3

BEGIN

IF (UPPER (@Order, 4))!= ' ASC ' and UPPER (right (@Order, 5))!= ' DESC ')

BEGIN PRINT (' err_02 ') return end

End

DECLARE @new_where1 VARCHAR (1000)

DECLARE @new_where2 VARCHAR (1000)

DECLARE @new_order1 VARCHAR (1000)

DECLARE @new_order2 VARCHAR (1000)

DECLARE @new_order3 VARCHAR (1000)

DECLARE @Sql VARCHAR (8000)

DECLARE @SqlCount NVARCHAR (4000)

IF ISNULL (@where, ') = '

BEGIN

SET @new_where1 = '

SET @new_where2 = ' WHERE '

End

ELSE

BEGIN

SET @new_where1 = ' WHERE ' + @where

SET @new_where2 = ' WHERE ' + @where + ' and '

End

IF ISNULL (@order, ') = ' or @SortType = 1 or @SortType = 2

BEGIN

IF @SortType = 1

BEGIN

SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC '

SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC '

End

IF @SortType = 2

BEGIN

SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC '

SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC '

End

End

ELSE

BEGIN

SET @new_order1 = ' ORDER BY ' + @Order

End

IF @SortType = 3 and CHARINDEX (', ' + @PrimaryKey + ', ', ' + @Order) >0

BEGIN

SET @new_order1 = ' ORDER BY ' + @Order

SET @new_order2 = @Order + ', '

SET @new_order2 = replace (replace (@new_order2, ' ASC, ', ' {ASC}, '), ' DESC, ', ' {DESC}, ')

SET @new_order2 = replace (replace (@new_order2, ' {ASC}, ', ' DESC, '), ' {DESC}, ', ' ASC, ')

SET @new_order2 = ' ORDER BY ' + SUBSTRING (@new_order2, 1,len (@new_order2)-1)

IF @FieldList <> ' * '

BEGIN

SET @new_order3 = replace (replace (@Order + ', ', ' ASC, ', ', '), ' DESC, ', ', ')

SET @FieldList = ', ' + @FieldList

While CHARINDEX (', ', @new_order3) >0

BEGIN

IF CHARINDEX (SUBSTRING (', ' + @new_order3, 1,charindex (', ', @new_order3)), ', ' + @FieldList + ', ') >0

BEGIN

SET @FieldList =

@FieldList + ', ' + SUBSTRING (@new_order3, 1,charindex (', ', @new_order3))

End

SET @new_order3 =

SUBSTRING (@new_order3, CHARINDEX (', ', @new_order3) +1,len (@new_order3))

End

SET @FieldList = SUBSTRING (@FieldList, 2,len (@FieldList))

End

End

SET @SqlCount = ' SELECT @TotalCount =count (*), @TotalPageCount =ceiling ((COUNT (*) +0.0)/'

+ CAST (@PageSize as VARCHAR) + ') from ' + @TableName + @new_where1

IF @RecorderCount = 0

BEGIN

EXEC sp_executesql @SqlCount, N ' @TotalCount int output, @TotalPageCount int output ',

@TotalCount output, @TotalPageCount output

End

ELSE

BEGIN

SELECT @TotalCount = @RecorderCount

End

IF @PageIndex > CEILING ((@TotalCount +0.0)/@PageSize)

BEGIN

SET @PageIndex = CEILING ((@TotalCount +0.0)/@PageSize)

End

IF @PageIndex = 1 OR @PageIndex >= CEILING ((@TotalCount +0.0)/@PageSize)

BEGIN

IF @PageIndex = 1--Returns the first page of data

BEGIN

SET @Sql = ' SELECT top ' + STR (@PageSize) + "+ @FieldList + ' from '

+ @TableName + @new_where1 + @new_order1

End

IF @PageIndex >= CEILING ((@TotalCount +0.0)/@PageSize)--Returns the last page of data

BEGIN

SET @Sql = ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' from ('

+ ' SELECT top ' + STR (ABS (@PageSize * @PageIndex-@TotalCount-@PageSize))

+ ' + @FieldList + ' from '

+ @TableName + @new_where1 + @new_order2 + ') as TMP '

+ @new_order1

End

End

ELSE

BEGIN

IF @SortType = 1-only primary key positive order ordering

BEGIN

IF @PageIndex <= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieval

BEGIN

SET @Sql = ' SELECT top ' + STR (@PageSize) + "+ @FieldList + ' from '

+ @TableName + @new_where2 + @PrimaryKey + ' > '

+ ' (select MAX (' + @PrimaryKey + ') from (select top)

+ STR (@PageSize * (@PageIndex-1)) + ' + @PrimaryKey

+ ' from ' + @TableName

+ @new_where1 + @new_order1 + ') as TMP ' + @new_order1

End

ELSE--Reverse retrieval

BEGIN

SET @Sql = ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' from ('

+ ' SELECT top ' + STR (@PageSize) + '

+ @FieldList + ' from '

+ @TableName + @new_where2 + @PrimaryKey + ' < '

+ ' (select MIN (' + @PrimaryKey + ') from (select top)

+ STR (@TotalCount-@PageSize * @PageIndex) + ' + @PrimaryKey

+ ' from ' + @TableName

+ @new_where1 + @new_order2 + ') as TMP ' + @new_order2

+ ') as TMP ' + @new_order1

End

End

IF @SortType = 2-primary key reverse ordering only

BEGIN

IF @PageIndex <= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieval

BEGIN

SET @Sql = ' SELECT top ' + STR (@PageSize) + "+ @FieldList + ' from '

+ @TableName + @new_where2 + @PrimaryKey + ' < '

+ ' (select MIN (' + @PrimaryKey + ') from (select top)

+ STR (@PageSize * (@PageIndex-1)) + ' + @PrimaryKey

+ ' from ' + @TableName

+ @new_where1 + @new_order1 + ') as TMP ' + @new_order1

End

ELSE--Reverse retrieval

BEGIN

SET @Sql = ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' from ('

+ ' SELECT top ' + STR (@PageSize) + '

+ @FieldList + ' from '

+ @TableName + @new_where2 + @PrimaryKey + ' > '

+ ' (select MAX (' + @PrimaryKey + ') from (select top)

+ STR (@TotalCount-@PageSize * @PageIndex) + ' + @PrimaryKey

+ ' from ' + @TableName

+ @new_where1 + @new_order2 + ') as TMP ' + @new_order2

+ ') as TMP ' + @new_order1

End

End

If @SortType = 3-Multiple column sort, must contain the primary key, and the last place is not processed

BEGIN

IF CHARINDEX (', ' + @PrimaryKey + ', ', ' + @Order) = 0

BEGIN PRINT (' err_02 ') return end

IF @PageIndex <= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieval

BEGIN

SET @Sql = ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' from ('

+ ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' from ('

+ ' SELECT top ' + STR (@PageSize * @PageIndex) + ' + @FieldList

+ ' from ' + @TableName + @new_where1 + @new_order1 + ') as TMP '

+ @new_order2 + ') as TMP ' + @new_order1

End

ELSE--Reverse retrieval

BEGIN

SET @Sql = ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' from ('

+ ' SELECT top ' + STR (@PageSize) + ' + @FieldList + ' from ('

+ ' SELECT top ' + STR (@TotalCount-@PageSize * @PageIndex + @PageSize) + ' + @FieldList

+ ' from ' + @TableName + @new_where1 + @new_order2 + ') as TMP '

+ @new_order1 + ') as TMP ' + @new_order1

End

End

End

EXEC (@Sql)

Go

2.

object:storedprocedure [dbo].    [Getrecordfrompage] Script date:07/23/2008 18:42:05 ******/
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
/*
Function Name: getrecordfrompage
function function: Gets the data for the specified page
Parameter description: @tblName The table name that contains the data
@fldName key field names
@PageSize number of records per page
@PageIndex the page number to get
@OrderType sort type, 0-Ascending, 1-Descending
@strWhere Query Criteria (Note: Do not add where)
*/
ALTER PROCEDURE [dbo]. [Getrecordfrompage]
@tblName varchar (255),--table name
@fldName varchar (255),--field name
@PageSize int = 10,--page size
@PageIndex int = 1,--page number
@OrderType bit = 0,--set sort type, not 0 value descending
@strWhere varchar (2000) = '--Query criteria (note: Do not add where)
As

DECLARE @strSQL varchar (6000)--subject sentence
DECLARE @strTmp varchar (1000)--Temporary variable
DECLARE @strOrder varchar (500)--Sort type

If @OrderType!= 0
Begin
Set @strTmp = ' < (select Min '
Set @strOrder = ' ORDER by [' + @fldName + '] desc '
End
Else
Begin
Set @strTmp = ' > select Max '
Set @strOrder = ' ORDER by [' + @fldName + '] ASC '
End

Set @str