SQL Paging stored Procedure implementation code

SQL Paging stored Procedure implementation code

Create PROCEDURE Sp_conn_sort
(
@tblName varchar (255),--table name

@strGetFields varchar (1000) = ' * ',--columns to be returned

@fldName varchar (255) = ',--sorted field name

@PageSize int = 40,--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 (*) as total from ' + @tblName + ' where ' + @strWhere

Else

Set @strSQL = ' SELECT count (*) as total from ' + @tblName

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 + ' + ' + @str Order

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

--The following code gives @strsql the SQL code to actually execute

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)

-----------------

SQL Server 2000 single primary key efficient paging stored procedure (multiple-field sorting is supported)

Create PROC P_viewpage


/**//*


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&gt;10 and Len (userid) &gt;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) &gt; 0 or CHARINDEX (', ', @Order) &gt; 0


BEGIN


SET @Order = REPLACE (@Order, ', ', ', ', ')


SET @Order = REPLACE (@Order, ', ', ', ', ')


End


IF ISNULL (@TableName, ') = ' or ISNULL (@FieldList, ') = '


or ISNULL (@PrimaryKey, ') = '


or @SortType &lt; 1 or @SortType &gt;3


or @RecorderCount &lt; 0 or @PageSize &lt; 0 or @PageIndex &lt; 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) &gt;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 &lt;&gt; ' * '


BEGIN


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


SET @FieldList = ', ' + @FieldList


While CHARINDEX (', ', @new_order3) &gt;0


BEGIN


IF CHARINDEX (SUBSTRING (', ' + @new_order3, 1,charindex (', ', @new_order3)), ', ' + @FieldList + ', ') &gt;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 (Select * from ' + @TableName + @new_where1 + ') as T '


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 &gt; CEILING ((@TotalCount +0.0)/@PageSize)


BEGIN


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


End


IF @PageIndex = 1 or @PageIndex &gt;= CEILING ((@TotalCount +0.0)/@PageSize)


BEGIN


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


BEGIN


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


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


End


IF @PageIndex &gt;= 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 &lt;= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieval


BEGIN


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


+ @TableName + @new_where2 + @PrimaryKey + ' &gt; '


+ ' (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 + ' &lt; '


+ ' (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 &lt;= CEILING ((@TotalCount +0.0)/@PageSize)/2--forward retrieval


BEGIN


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


+ @TableName + @new_where2 + @PrimaryKey + ' &lt; '


+ ' (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 + ' &gt; '


+ ' (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 &lt;= 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


PRINT (@SQL)


EXEC (@Sql)