Aspnetpager the paging control stored procedure _mssql

Source: Internet
Author: User
Tags one table rowcount
Copy Code code as follows:

SET QUOTED_IDENTIFIER ON
Go
SET ANSI_NULLS on
Go

ALTER PROCEDURE Usp_paginglarge
@TableNames VARCHAR (200)--table name, can be multiple tables, but cannot be aliased
@PrimaryKey VARCHAR (100),--primary key, can be null, but @order is empty when the value cannot be null
@Fields VARCHAR (200),--the field to be fetched, can be a field of more than one table, can be empty, empty to represent a select *
@PageSize INT,--Number of records per page
@CurrentPage INT,--current page, 0 represents page 1th
@Filter VARCHAR (200) = ',--condition, can be empty without filling in where
@Group VARCHAR (200) = ',--group by, can be empty, without filling group by
@Order VARCHAR (200) = '---sort, can be empty, default by primary key in ascending order, not filled in by
As
BEGIN
DECLARE @SortColumn VARCHAR (200)
DECLARE @Operator CHAR (2)
DECLARE @SortTable VARCHAR (200)
DECLARE @SortName VARCHAR (200)
IF @Fields = '
SET @Fields = ' * '
IF @Filter = '
SET @Filter = ' WHERE 1=1 '
ELSE
SET @Filter = ' WHERE ' + @Filter
IF @Group <> '
SET @Group = ' Group by ' + @Group

IF @Order <> '
BEGIN
DECLARE @pos1 int, @pos2 int
SET @Order = replace (replace (@Order, ' ASC ', ' ASC '), ' desc ', ' desc ')
IF CHARINDEX (' DESC ', @Order) > 0
IF CHARINDEX (' ASC ', @Order) > 0
BEGIN
IF CHARINDEX (' DESC ', @Order) < CHARINDEX (' ASC ', @Order)
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
End
ELSE
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
SET @SortColumn = replace (replace (@Order, ' ASC ', '), ' DESC ', '), ', '
SET @pos1 = CHARINDEX (', ', @SortColumn)
IF @pos1 > 0
SET @SortColumn = SUBSTRING (@SortColumn, 1, @pos1-1)
SET @pos2 = CHARINDEX ('. ', @SortColumn)
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING (@SortColumn, 1, @pos2-1)
IF @pos1 > 0
SET @SortName = SUBSTRING (@SortColumn, @pos2 +1, @pos1-@pos2-1)
ELSE
SET @SortName = SUBSTRING (@SortColumn, @pos2 +1, LEN (@SortColumn)-@pos2)
End
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
End
End
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = ' >= '
End


DECLARE @type varchar (50)
DECLARE @prec int
SELECT @type =t.name, @prec =c.prec
From sysobjects o
JOIN syscolumns C on o.id=c.id
JOIN systypes T on C.xusertype=t.xusertype
WHERE o.name = @SortTable and c.name = @SortName




IF CHARINDEX (' char ', @type) > 0
SET @type = @type + ' (' + CAST (@prec as varchar) + ') '


DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1

PRINT @type
DECLARE @sql NVARCHAR (4000)

SET @Sql = ' DECLARE @SortColumnBegin ' + @type + '
SET RowCount ' + Cast (@TopRows as VARCHAR) + ' SELECT @SortColumnBegin = ' +
@SortColumn + ' from ' + @TableNames + ' + @Filter + ' + @Group + ' ORDER BY ' + @Order + '
SET RowCount ' + CAST (@PageSize as VARCHAR (10)) + '
SELECT ' + @Fields + ' from ' + @TableNames + ' + @Filter + ' and ' + @SortColumn + ' + @Operator + ' @SortColumnBegin ' + ISNULL (@Group, ') + ' ORDER BY ' + @Order + '

--Print (@sql)
Exec (@sql)


End

Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go

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.