The simplest net paging Program

The simplest net paging Program

CREATE procedure [dbo]. [sp_getPageControl]
(
@ TableNames nvarchar (200), -- table name, which can be multiple tables but cannot be aliases
@ PrimaryKey nvarchar (100), -- primary key, which can be null, but this value cannot be blank when @ Order is null
@ Fields nvarchar (1000) = '*', -- the field to be retrieved. It can be a field of multiple tables. It can be blank. If it is null, it indicates select *
@ PageSize int, -- number of records per page
@ CurrentPage int, -- current page, 0 indicates page 1st
@ RecNums int output, -- number of records
@ PageNums int output, -- page number
@ Filter nvarchar (1000) = '', -- condition, which can be null. Do not enter where
@ Group nvarchar (200) = '', -- group basis. It can be empty. You do not need to enter Group
@ Order nvarchar (200) = ''-- sort. It can be null. If it is null, It is sorted by the primary key in ascending order by default. Order by is not required.
)
As
Set nocount on
Begin
Declare @ SortColumn nvarchar (1000)
Declare @ Operator nchar (2)
Declare @ SortTable nvarchar (200)
Declare @ SortName nvarchar (200)
Declare @ SQL nvarchar (4000)
Declare @ sqlTemp nvarchar (4000)

If @ Fields =''
Set @ Fields = '*'
If @ Filter =''
Set @ Filter = 'where 1 = 1'
Else
Set @ Filter = 'where 1 = 1 and' + @ Filter
If @ Group <>''
Set @ Group = 'group by' + @ GROUP

If @ Order <>''
Begin
Declare @ pos1 int, @ pos2 int
Set @ Order = 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 (@ 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 nvarchar (50)
Declare @ prec int
Select @ type = t. name, @ prec = c. prec
From sysobjects o
Join syscolumns c on o. id = c. id
Join policypes 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 nvarchar) + ')'

Declare @ TopRows int
Set @ TopRows = @ PageSize * @ CurrentPage + 1
-- Print @ TopRows
-- Print @ Operator

-- Count records
Set @ sqlTemp = 'select @ counts = count (*) from '+ @ TableNames + ''+ @ Filter

---- Total number of retrieved query results -----
Exec sp_executesql @ sqlTemp, n' @ counts int out', @ RecNums out

-- Retrieve the total number of pages
If @ RecNums <= @ pageSize
Set @ PageNums = 1
Else
Set @ PageNums = (@ RecNums/@ pageSize) + 1

Select @ SQL = 'Clare @ SortColumnBegin '+ @ type +'
Set rowcount '+ convert (char, @ TopRows) +'
Select @ SortColumnBegin = '+ @ SortColumn + 'from' + @ TableNames + ''+ @ Filter +'' + @ Group + 'ORDER BY' + @ order +'
Set rowcount '+ convert (char, @ PageSize) +'
Select '+ @ Fields + 'from' + @ TableNames + ''+ @ Filter + 'and' + @ SortColumn +'' + @ Operator +' @ SortColumnBegin '+ @ Group + 'ORDER BY' + @ order

-- Print @ SQL
------ Return query result -----
Exec sp_executesql @ SQL
End

What do you think after reading this paging program? Is it the simplest.