Copy Code code as follows:
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: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
How to invoke the above stored procedure with vc#
current 1/2 page
1 2 Next read the full text
