My paging Stored Procedure

Create procedure [DBO]. [proc_datapagelist2000] (

@ Tablename varchar (255 ),

@ Getfields varchar (1000) = '*',

@ Keyid varchar (50) = 'id ',

@ Strorder varchar (255) = '',

@ Strwhere varchar (1500) = '',

@ Pageindex int,

@ Pagesize int,

@ Iscount Int = 0

)

Set nocount on

Declare @ strsql varchar (8000)

Declare @ temprowcount int

Declare @ totalpages int

Declare @ currentpagesize int

Declare @ strorder2 varchar (200)

Declare @ rowcount int

If @ pageindex <1

Begin

Raiserror ('The incoming page number or number of records per page is incorrect! ', 16, 1)

Return

End

If not (@ strwhere is null or rtrim (@ strwhere) = '')

Set @ strwhere = 'where' + @ strwhere

If not (@ strorder is null or rtrim (@ strorder) = '')

Set @ strorder = 'ORDER BY' + @ strorder

Else

Set @ strorder = 'ORDER BY' + @ keyid + 'desc'

Declare @ isgroupby int

If charindex ('group by', lower (@ strwhere)> 0 begin

Set @ isgroupby = 1

End else begin

Set @ isgroupby = 0

End

If (@ iscount = 1)

Begin

Set @ strsql = 'select count (*) as totalcount from '+ @ tablename + @ strwhere

If @ isgroupby = 1 begin

Set @ strsql = 'select count (*) as totalcount from (select count (1) as a from '+ @ tablename + @ strwhere +') as TT1'

End

Exec (@ strsql)

End

Else

Begin

If (@ pageindex = 1)

If @ pagesize =-1 begin

Set @ strsql = 'select' + @ getfields

Set @ strsql = @ strsql + 'from ['+ @ tablename +'] With (nolock) '+ @ strwhere + @ strorder

End

Else

Begin

Set @ strsql = 'select top' + convert (varchar (10), @ pagesize) + ''+ @ getfields

Set @ strsql = @ strsql + 'from ['+ @ tablename +'] With (nolock) '+ @ strwhere + @ strorder

End

Else

Begin

Set @ strorder = upper (@ strorder)

Set @ strorder2 = Replace (@ strorder, 'desc', 'sc1 ')

Set @ strorder2 = Replace (@ strorder2, 'asc ', 'desc ')

Set @ strorder2 = Replace (@ strorder2, 'sc1', 'asc ')

Declare @ SQL nvarchar (500)

Set @ SQL = 'select @ maxcount = count (*) from ['+ @ tablename +'] '+ @ strwhere

If @ isgroupby = 1 begin

Set @ SQL = 'select @ maxcount = count (*) from (select count (1) as a from '+ @ tablename + @ strwhere +') as tt2'

End

Exec sp_executesql @ SQL, n' @ maxcount int output', @ rowcount output

Set @ totalpages = case when @ rowcount % @ pagesize = 0 then @ rowcount/@ pagesize else @ rowcount/@ pagesize + 1 end

If (@ pageindex >=@ totalpages)

Begin

Set @ currentpagesize = @ rowcount-(@ pageindex-1) * @ pagesize

While (@ currentpagesize <0)

Begin

Set @ currentpagesize = @ pagesize + @ currentpagesize

End

Set @ strsql = 'select * from ('+ char (13)

Set @ strsql = @ strsql + 'select top' + convert (varchar (10), @ currentpagesize) + ''+ @ getfields + 'from

['+ @ Tablename +'] With (nolock) '+ @ strwhere + @ strorder2 + char (13)

Set @ strsql = @ strsql + ') as a' + @ strorder

End

Else

Begin

If (@ pageindex <= @ totalpages/2)

Begin

Set @ temprowcount = @ pageindex * @ pagesize

Set @ strsql = 'select * from ('+ char (13)

Set @ strsql = @ strsql + 'select top' + convert (varchar (10), @ pagesize) + '* from (' + char (13)

Set @ strsql = @ strsql + 'select top' + convert (varchar (10), @ temprowcount) + ''+ @ getfields + 'from

['+ @ Tablename +'] With (nolock) '+ @ strwhere + @ strorder

Set @ strsql = @ strsql + ') as a1' + @ strorder2 + char (13)

Set @ strsql = @ strsql + ') as a' + @ strorder

End

Else

Begin

Set @ temprowcount = @ rowcount-(@ pageindex-1) * @ pagesize

Set @ strsql = 'select * from ('+ char (13)

Set @ strsql = @ strsql + 'select top' + convert (varchar (10), @ pagesize) + '* from (' + char (13)

Set @ strsql = @ strsql + 'select top' + convert (varchar (10), @ temprowcount) + ''+ @ getfields + 'from

['+ @ Tablename +'] With (nolock) '+ @ strwhere + @ strorder2

Set @ strsql = @ strsql + ') as a1' + @ strorder + char (13)

Set @ strsql = @ strsql + ') as'

End

End

End

Exec (@ strsql)

End

 

Paste the source code below

Source code