SQL Server stored procedure paging code 1th/2 page _mssql

Source: Internet
Author: User
Tags rtrim
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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.