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
