Code
---- Simple paging Stored Procedure
---- 2009.12.9
---- Liuwm
Create proc sppagedselect
@ Table varchar (1000), -- table name, which can be an SQL statement
@ Page int, -- current page
@ Pagesize int, -- page size
@ Key varchar (50), -- primary key in the table
@ Field varchar (50), -- sort column names
@ Sort varchar (4) -- sort by ASC or DESC
As
Begin
Declare @ sqlstr varchar (5000)
Set @ sqlstr = 'select top '+ Cast (@ pagesize as varchar (10 )) + '* from' + @ table + 'where' + @ key +' not in (select top '+ Cast (@ pagesize * (@ page-1 )) as varchar (10) + ''+ @ key + 'from' + @ table + 'ORDER BY' + @ field +'' + @ sort + ') order by '+ @ field + ''+ @ sort
Print @ sqlstr
Exec (@ sqlstr)
End
--- Usage is as follows --
Exec sppagedselect '(select sum (amount) as sales, Region ID
From hhc_h_order
Where year (adddate) = 2009
Group by Region ID) as '', 'region id', 'sales', 'desc'
-- Note that the preceding sorting field sales is obtained from the sales statement after the SQL statement, not from the physical table.
Exec sppagedselect 'job', 1, 3, 'job _ id', 'avg (job_id) ', 'asc'
