Create proc pagingforall
@ Tablename nvarchar (50), --- table
@ Columns nvarchar (500), --- query Column
@ Where nvarchar (500) = '', --- query Condition
@ Identitycolumn nvarchar (50), --- column, growth column, generally primary key
@ Orderby nvarchar (50) = '', -- sort by which column
@ Pageindex int, ---- page n
@ Pagesize Int = 50 ---- number of rows per page
As
Set nocount on
Declare @ SQL nvarchar (1000)
If Len (@ orderby) = 0
Set @ orderby = @ identitycolumn
Set @ SQL = 'select top '+ convert (nvarchar (10), @ pagesize) + ''+ @ columns + 'from' + @ tablename + 'where 1 = 1' + @ where + 'and' + @ identitycolumn + '>
(Select isnull (max ('+ @ identitycolumn +'), 0) from
(Select top '+ convert (nvarchar (10), @ pagesize * (@ PageIndex-1) + ''+ @ identitycolumn +' from ['+ @ tablename +']
Where 1 = 1' + @ where + 'ORDER BY' + @ orderby + ') T
)
'
Exec (@ SQL)
Set nocount off
Go