For more common paging of stored procedures, you need to give more advice!

 
-- Name: Create a paging Stored Procedure
-- Time: 2011-07-04
-- Author: Wuhan
Alter proc proc_wuhan_getpages -- create a stored procedure
@ Pagesize int, -- page size
@ Currentpage int, -- current page (page number)
@ Tablename nvarchar (100), -- table name
@ Columns nvarchar (1000), -- field name
@ Condition nvarchar (100), -- Query Condition
@ Ordertype int, -- sort type (0 is ascending, 1 is descending)
@ Ordercolumn nvarchar (100), -- Sort Field
@ Premarykeycolumn nvarchar (100) -- primary key field name
As
Begin
Declare @ strsqlstatement nvarchar (2000) -- the last statement executed by the Stored Procedure
Declare @ chartype nvarchar (10)
Begin
If @ condition = ''-- the query condition is null.
Begin
If @ ordertype = 0 -- Ascending
Begin
Set @ chartype = 'asc'
Set @ strsqlstatement = 'select top' + ltrim (@ pagesize) +'
'+ @ Columns + 'from' + @ tablename + 'where ('
+ @ Premarykeycolumn + 'not in (select top '+ convert (char, @ pagesize * (@ currentPage-1) + @ premarykeycolumn
+ 'From' + @ tablename + 'ORDER BY' + @ ordercolumn + @ chartype + ') Order'
+ @ Ordercolumn + @ chartype
End
Else if @ ordertype = 1
Begin
Set @ chartype = 'desc' -- descending order
Set @ strsqlstatement = 'select top' + ltrim (@ pagesize) +'
'+ @ Columns + 'from' + @ tablename + 'where ('
+ @ Premarykeycolumn + 'not in (select top '+ convert (char, @ pagesize * (@ currentPage-1) + @ premarykeycolumn
+ 'From' + @ tablename + 'ORDER BY' + @ ordercolumn + @ chartype + ') Order'
+ @ Ordercolumn + @ chartype
End
Print @ strsqlstatement
Exec (@ strsqlstatement) -- output the last statement to be executed
End
Else if @ condition! = ''-- The query condition is not empty.
Begin
If @ ordertype = 0 -- Ascending
Begin
Set @ chartype = 'asc'
Set @ strsqlstatement = 'select top' + ltrim (@ pagesize) +'
'+ @ Columns + 'from' + @ tablename + 'where ('
+ @ Premarykeycolumn + 'not in (select top '+ convert (char, @ pagesize * (@ currentPage-1) + @ premarykeycolumn
+ 'From' + @ tablename + 'ORDER BY' + @ ordercolumn + @ chartype + ') and' + @ condition + ') Order'
+ @ Ordercolumn + @ chartype
End
Else if @ ordertype = 1
Begin
Set @ chartype = 'desc' -- descending order
Set @ strsqlstatement = 'select top' + ltrim (@ pagesize) +'
'+ @ Columns + 'from' + @ tablename + 'where ('
+ @ Premarykeycolumn + 'not in (select top '+ convert (char, @ pagesize * (@ currentPage-1) + @ premarykeycolumn
+ 'From' + @ tablename + 'ORDER BY' + @ ordercolumn + @ chartype + ') and' + @ condition + ') Order'
+ @ Ordercolumn + @ chartype
End
Print @ strsqlstatement
Exec (@ strsqlstatement) -- output the last statement to be executed
End
Else
Print 'An error occurred. I still need to practice more! '+ Ltrim (@ pagesize)
End
End