A paging stored procedure that combines not

Stored Procedures | paging
Paging stored procedures, their own "not_in" and "SET rowcount" a combination of two ways.
The "SET rowcount" algorithm does not change, and the "not_in" sort adds a primary key field so that the speed increases a lot and the result is one-way only, but not reversible. "SET rowcount" does not have a speed problem, "not_in" sort after the primary key field in the 30W record on the non-indexed field, the sort of non-aggregate field to flip a page but 3 seconds, also OK: P
－－－－－－－－－－－－－－－－－－－－－－
CREATE PROCEDURE Paging_custom
(
@Tables varchar (1000),
@PK varchar (100),
@Sort varchar = NULL,
@isAscending Bit=1,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar (1000) = ' * ',
@Filter varchar (1000) = NULL,
@Group varchar (1000) = NULL)
As

/*default sorting*/
IF @Sort is NULL OR @Sort = '
SET @Sort = @PK

/*find the @SORT type*/
DECLARE @strFilter varchar (1000)
DECLARE @strSimpleFilter varchar (1000)
DECLARE @strGroup varchar (1000)

DECLARE @SortTable varchar (100)
DECLARE @SortName varchar (100)
DECLARE @strPKColumn varchar (200)
DECLARE @strSortColumn varchar (200)
DECLARE @operator char (2)

/*set Filter & Group variables.*/
IF @Filter is not NULL and @Filter!= '
BEGIN
SET @strFilter = ' WHERE ' + @Filter + '
SET @strSimpleFilter = ' and ' + @Filter + '
End
ELSE
BEGIN
SET @strSimpleFilter = '
SET @strFilter = '
End
IF @Group is not NULL and @Group!= '
SET @strGroup = ' GROUP by ' + @Group + '
ELSE
Begin
SET @strGroup = '
End

/*count*/
EXEC (' SELECT COUNT (*) from ' + @Tables + ' + @strFilter)

/*set sorting variables.*/
SET @strSortColumn = @Sort

/*operator and asc_desc*/
DECLARE @strAsc_Des varchar (10)
IF @isAscending = 0
BEGIN

SET @operator = ' <= '
SET @strAsc_Des = ' Desc '
End
ELSE
BEGIN
SET @operator = ' >= '
SET @strAsc_Des = ' ASC '
End
IF CHARINDEX ('. ', @PK) > 0
BEGIN
SET @strPKColumn = SUBSTRING (@PK, 0, CHARINDEX ('. ', @PK))
End
ELSE
BEGIN
SET @strPKColumn = @PK
End
IF CHARINDEX ('. ', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING (@strSortColumn, 0, CHARINDEX ('. ', @strSortColumn))
SET @SortName = SUBSTRING (@strSortColumn, CHARINDEX ('. ', @strSortColumn) + 1, LEN (@strSortColumn))
End
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
End

/*handler Complex table*/
--be Join Table,so get the left table
IF CHARINDEX (' join ', @Tables) > 0
BEGIN
SET @SortTable =substring (LTrim (@Tables), 0,charindex (", @Tables)"
SET @strSortColumn = @SortTable + '. ' + @SortName
End


/*check the SortColumn if be unique*/
DECLARE @tempName varchar (100)
IF @SortName <> @strPKColumn
Begin
SELECT @tempName =b.name
From sysobjects a INNER JOIN
sysobjects b on a.id = B.parent_obj
INNER join sysindexes c on b.name = C.name INNER Join
Sysindexkeys D on c.id = d.id and C.indid = D.indid INNER JOIN
syscolumns e on d.id = e.id and d.colid = E.colid
WHERE (B.xtype = ' UQ ') and (A.name = @SortTable) and (e.name = @SortName)

IF @tempName is NULL goto:paging_not_in
End

DECLARE @type varchar (100)
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

/*the left table doesn ' t contain sortcolumn * *
--if @type is null or @type = ' goto:paging_not_in

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

DECLARE @strPageSize varchar (50)
DECLARE @strStartRow varchar (50)


/*default Page number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*set Paging variables.*/
SET @strPageSize = CAST (@PageSize as varchar (50))
SET @strStartRow = CAST ((@PageNumber-1) * @PageSize + 1) as varchar (50))

/*execute Dynamic query*/

EXEC (' DECLARE @SortColumn ' + @type + '
SET RowCount ' + @strStartRow +
' SELECT @SortColumn = ' + @strSortColumn + ' from ' + @Tables + @strFilter + ' + @strGroup +
' ORDER BY ' + @Sort + @strAsc_Des + ' SET rowcount ' + @strPageSize +
' SELECT ' + @Fields + ' from ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' +
@strSimpleFilter + ' + @strGroup + ' ORDER BY ' + @Sort + @strAsc_Des
)
Return

Paging_not_in:
--declare @strPageSize varchar (50)
--set @strPageSize = CAST (@PageSize as varchar (50))
DECLARE @strTotalNum int
SET @strTotalNum = (@PageNumber-1) * @PageSize
--The first page
--declare @strSQL varchar (8000)
IF @strTotalNum = 0
Begin
EXEC (' select top ' + @PageSize + ' + @Fields + ' from ' + @Tables + ' + @strFilter + @strGroup + ' ORDER BY ' + @Sort + @strAsc_Des)
End
Else
Begin
EXEC (' select top ' + @PageSize + ' + @Fields + ' from ' + @Tables + ' where '
+ @PK + ' not in (select Top ')
+ @strTotalNum + ' + @PK + ' from ' + @Tables + ' + @strFilter + @strGroup +
' ORDER BY ' + @Sort + @strAsc_Des
+ ') ' + @strSimpleFilter + @strGroup + ' ORDER BY ' + @Sort + @strAsc_Des)
End

Go