分页存储过程 在网站设计，网页开发中，是要被经常遇到的。
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure main_table_sdiv
(
@pagesize int,
@pageindex int,
@docount bit,
@TableName varchar(20),
@Col varchar(500),
@sTableName varchar(20),
@whereStr varchar(100)
)
as
declare @SqlStr varchar(1000)
if(@docount=1)
begin
set @SqlStr = 'select count(id) from ' + @TableName
exec(@SqlStr)
end
else
begin
create table #indextable
(
[id] [int] IDENTITY (1, 1) NOT NULL ,
nid int
)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound = ( @pageindex - 1 ) * @pagesize
set @PageUpperBound = @PageLowerBound + @pagesize
set rowcount @PageUpperBound
set @SqlStr = 'insert into #indextable (nid) select id from ' +@TableName+ ' order by id desc '
--print @SqlStr
exec(@SqlStr)
set @SqlStr = 'select a.id'+ @Col +' from '+@TableName+' a,#indextable t '+@sTableName+' where a.id = t.nid '
set @SqlStr = @SqlStr + ' and t.id >'+cast(@PageLowerBound as varchar(20))+' and t.id <= '+cast(@PageUpperBound as varchar(20))+ ' ' + @whereStr + ' order by t.id '
--print @SqlStr
exec(@SqlStr)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--多表分页查询
--DECLARE @pagesize int
--DECLARE @pageindex int
--DECLARE @docount bit
--DECLARE @TableName varchar(20)
--DECLARE @Col varchar(500)
--DECLARE @sTableName varchar(20)
--DECLARE @whereStr varchar(100)
--set @pagesize = 25
--set @pageindex = 4
--set @docount = 0
--set @TableName = 'Firstunion' 主表
--set @Col = ',a.sp,a.userindexid,a.Reguserid,unionuser.id,unionuser.nickname'
--set @sTableName = ',unionuser' 从表
--set @whereStr = ' and a.userindexid = unionuser.id'
--EXEC [main_table_sdiv] @pagesize, @pageindex, @docount, @TableName, @Col, @sTableName, @whereStr
--set @pagesize = 25
--set @pageindex = 4
--set @docount = 0
--set @TableName = 'Firstunion'
--set @Col = ',a.sp,a.userindexid,a.Reguserid,u.id,u.nickname'
--set @sTableName = ',unionuser u'
--set @whereStr = ' and a.userindexid = u.id'
--EXEC [main_table_sdiv] @pagesize, @pageindex, @docount, @TableName, @Col, @sTableName, @whereStr
--单表分页查询
--set @pagesize = 25
--set @pageindex = 3
--set @docount = 0
--set @TableName = 'Firstunion'
--set @Col = ',a.sp,a.userindexid,a.Reguserid'
--set @sTableName = ''
--set @whereStr = ''
--EXEC [main_table_sdiv] @pagesize, @pageindex, @docount, @TableName, @Col, @sTableName, @whereStr