高效率jsp+mssql存儲過程通用數據庫分頁
高效率jsp教程+mssql存儲過程通用數據庫教程分頁
CREATE PROCEDURE Ture_Page
@PageSize int, --每頁的行數
@PageIndex int, --1 代表第一頁
@Col varchar(200), --要顯示的字段
@Table varchar(200), --所用到的表,復條的話就寫from與where之間的內容
@Where varchar(200)= ' ', --所用到的條件
@OKey varchar(50), --排序字段
@Order varchar(20)= 'ASC ' --排序方式
as
declare @cmdstr varchar(2000)
set nocount on
set @cmdstr= 'select top '
set @cmdstr=@cmdstr+convert(nvarchar,@PageSize)
if @Order= 'DESC ' and @PageIndex> 1
set @cmdstr=@cmdstr+ ' '+@Col+ ' from '+@Table+ ' where '+@OKey+ ' < '
else if @PageIndex=1
set @cmdstr=@cmdstr+ ' '+@Col+ ' from '+@Table+ ' where '+@OKey+ '> = '
else
set @cmdstr=@cmdstr+ ' '+@Col+ ' from '+@Table+ ' where '+@OKey+ '> '
if @PageIndex> 1
begin
if @Order= 'ASC '
set @cmdstr=@cmdstr+ '(select max ( '+@OKey+ ') from (select top '
else
set @cmdstr=@cmdstr+ '(select min ( '+@OKey+ ') from (select top '
set @cmdstr=@cmdstr+convert(nvarchar,(@PageIndex-1)*@PageSize)
set @cmdstr=@cmdstr+ ' '+@OKey+ ' from '+@Table+ ' order by '+@OKey+ ' '+@Order+ ') as t) '
end
else
set @cmdstr=@cmdstr+ '0 '
if @Where <> ' '
set @cmdstr=(@cmdstr+ ' and '+@Where+ ' order by '+@OKey+ ' '+@Order)
else
set @cmdstr=(@cmdstr+ 'order by '+@OKey+ ' '+@Order)
print @cmdstr
exec(@cmdstr)
set nocount off
GO %>
