Code
Alter PROCEDURE Pages
@ TableNames VARCHAR (200), -- table name, which can be multiple tables but cannot be aliases
@ PrimaryKey VARCHAR (100), -- primary key, which can be null, but this value cannot be blank when @ Order is null
@ Fields VARCHAR (800), -- the field to be retrieved. It can be a field of multiple tables. It can be null. If it is null, it indicates select *
@ PageSize INT, -- number of records per page
@ CurrentPage INT, -- current page, 0 indicates page 1st
@ Filter VARCHAR (200) = '', -- condition, which can be null. Do not enter where
@ Order VARCHAR (200) = '', -- sort. It can be null. If it is null, It is sorted by the master key in ascending order by default. Order by is not required.
@ ResultCount varchar (24)
AS
BEGIN
Declare @ topRow varchar (12)
Declare @ tempPageSize varchar (12)
If (len (@ Order)> 0)
Begin
Set @ Order = 'ORDER BY' + @ order
End
Else
Begin
Set @ Order =''
End
If (len (@ Filter) <1)
Begin
Set @ Filter = '1 = 1'
End
If (@ CurrentPage-1 <= 0)
Set @ CurrentPage = 0
If (len (rtrim (ltrim (@ ResultCount)> 0)
Set @ ResultCount = 'set rowcount' + ltrim (rtrim (@ ResultCount ))
Set @ topRow = rtrim (ltrim (str (@ PageSize * (@ CurrentPage-1 ))))
Set @ tempPageSize = rtrim (ltrim (str (@ PageSize )))
Exec ('
Declare @ temptable table (rownum int identity (1, 1), Gid varchar (36) '+'
Declare @ datatable table (Gid varchar (36) '+'
Declare @ date datetime '+'
Set @ date = getdate () '+'
Set nocount on '+'
'+ @ ResultCount +'
Insert into @ temptable (Gid) select '+ @ PrimaryKey + 'from' + @ TableNames + 'where' + @ Filter + @ Order +'
Set rowcount '+ @ tempPageSize +'
Insert into @ datatable (Gid) select Gid from @ temptable where rownum> '+ @ topRow +'
Select '+ @ Fields + 'from' + @ TableNames + 'where' + @ Filter + 'and' + @ PrimaryKey +' in (select Gid from @ datatable) '+ @ Order +'
Set rowcount 0' +'
Print (datediff (MS, @ date, getdate ()))')
-- Declare @ datatable table (Gid varchar (12) '+'
--- Insert into @ datatable (Gid) select Gid from @ temptable where rownum> '+ @ topRow +'
-- Set rowcount '+ @ tempPageSize +'
-- + 'Set rowcount 0'
-- Print ('insert into @ temptable (Gid) select' + @ PrimaryKey + 'from' + @ TableNames + 'where' + @ Filter + @ Order)
--- Print ('select' + @ Fields + 'from' + @ TableNames + 'where' + @ Filter + 'and' + @ PrimaryKey + 'in (select Gid from @ datatable) '+ @ Order)
End
GO
