多表查询的分页存储过程？

[dabin130]

那位大哥有多表查询的分页存储过程 给点代码 例子 谢过了。。。。急用哦！

[takako_mu]

ALTER proc [dbo].[usp_daily_work_select]
@flag int,
@owner nvarchar(50),
@status nvarchar(50),
@project nvarchar(50),
@startdate1 datetime,
@startdate2 datetime,
@duedate1 datetime,
@duedate2 datetime,
@description nvarchar(2000),
@updatedatetime datetime,
@updatename nvarchar(50),
@type nvarchar(50),
@item nvarchar(50),
@code nvarchar(50),
@pagesize int,
@pageindex int


AS
Begin

--查询时显示的资料
if(@flag=1)
begin
if(@status='Delete')
begin
with temptbl as
(
select ROW_NUMBER()over(order by project asc,(right([Code],5)) desc) AS Number, 
		dw.[No],dw.Project,dw.Item,dw.Code,dw.[Type],dw.[Description (purpose/value)],dw.Status,dw.[%],
        Convert(Varchar(10),dw.[Start Date],120)AS [Start Date],Convert(Varchar(10),dw.[Due Date],120)AS [Due Date],dw.Owner,dw.[Day/Hour],dw.Comment,dw.Updatedatetime,dw.UpdateName
from daily_work dw
where dw.Status='Delete'
	and dw.Item like '%'+@item+'%'
	and	dw.Owner like ''+@owner+'%' 
	and dw.Status like '%'+@status+'%'
	and dw.Project like '%'+@project+'%'
	and ((dw.[Start Date] between @startdate1 and @startdate2) or dw.[Start Date] IS null)
	and ((dw.[Due Date] between @duedate1 and @duedate2 )or dw.[Due Date] IS null)
	and dw.[Description (purpose/value)] like '%'+@description+'%'
)
select * from temptbl where Number>=((@pageindex-1)*@pagesize+1)
and Number<=(@pageindex*@pagesize) order by project asc,(right([Code],5)) desc
end

else
begin
with temptbl as
(
select 
        ROW_NUMBER()over(order by project asc,(right([Code],5)) desc) AS Number, 
		dw.[No],dw.Project,dw.Item,dw.Code,dw.[Type],dw.[Description (purpose/value)],dw.Status,dw.[%],
        Convert(Varchar(10),dw.[Start Date],120)AS [Start Date],Convert(Varchar(10),dw.[Due Date],120)AS [Due Date],dw.Owner,dw.[Day/Hour],dw.Comment,dw.Updatedatetime,dw.UpdateName
from daily_work dw
where dw.Owner like ''+@owner+'%' 
	and dw.Status like '%'+@status+'%'
	and dw.Item like '%'+@item+'%'
	and dw.Project like '%'+@project+'%'
	and ((dw.[Start Date] between @startdate1 and @startdate2) or dw.[Start Date] IS null)
	and ((dw.[Due Date] between @duedate1 and @duedate2 )or dw.[Due Date] IS null)
	and dw.[Description (purpose/value)] like '%'+@description+'%'
	and dw.Status<>'Delete'
)
select * from temptbl where Number>=((@pageindex-1)*@pagesize+1)
and Number<=(@pageindex*@pagesize) order by project asc,(right([Code],5)) desc
end
end

[dabin130]

来点注释啊！呜呜！

[BossFriday]

将你的所谓多表做成一个视图,然后用通用的分页procedure分页.

[果然如此]

来源：http://hi.baidu.com/grrc/blog/item/da5d54c63587561c9c163d46.html
--通用分页存储过程
alter PROCEDURE [dbo].[sp_GetRecordFromPage]
@TableName varchar(350), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1, --指定当前为第几页
@distinct VARCHAR(50)=NULL, --去除重复值，注意只能是一个字段
@top INT=NULL --查询TOP,不传为全部
AS
BEGIN
Declare @sql nvarchar(4000);
Declare @totalRecord int;
DECLARE @totalPage INT;
--计算总记录数
IF (@distinct IS NULL OR @distinct='') BEGIN
IF (@SqlWhere='' OR @sqlWhere IS NULL)
SET @sql = 'select @totalRecord = count(1) from ' + @TableName
ELSE
SET @sql = 'select @totalRecord = count(1) from ' + @TableName + ' where ' + @sqlWhere
END ELSE BEGIN
IF (@SqlWhere='' OR @sqlWhere IS NULL)
SET @sql = 'select @totalRecord = count(distinct ' + @distinct + ') from ' + @TableName
ELSE
SET @sql = 'select @totalRecord = count(distinct ' + @distinct + ') from ' + @TableName + ' where ' + @sqlWhere
END

EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数


IF(@top>0) BEGIN
--指定TOP 记录
SET @Fields= 'top ' + CAST(@top AS VARCHAR(20)) + ' ' + @Fields;
--如果总记录数超过TOP数,设总记录数为TOP数
IF(@totalRecord>@top)
SET @totalRecord=@top
END


IF (@distinct IS NULL OR @distinct='')
BEGIN
IF (@SqlWhere='' or @sqlWhere IS NULL)
SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName
ELSE
SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @SqlWhere
END
ELSE
BEGIN
IF (@SqlWhere='' or @sqlWhere IS NULL)
SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName
ELSE
SET @sql = 'Select * FROM (select ' + @Fields + ',ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId from ' + @TableName + ' where ' + @SqlWhere
SET @sql=@sql + ' GROUP BY ' + @distinct;
END

--计算总页数
SELECT @totalPage=CEILING((@totalRecord+0.0)/@PageSize)

--处理页数超出范围情况
IF @PageIndex<=0
SET @pageIndex = 1
IF @PageIndex>@totalPage SET @pageIndex=@totalPage

--处理开始点和结束点
DECLARE @StartRecord INT
DECLARE @EndRecord int

SET @StartRecord = (@pageIndex-1)*@PageSize + 1
SET @EndRecord = @StartRecord + @pageSize - 1

--继续合成sql语句
SET @sql = @sql + ') as tempTable where rowId >=' + CONVERT(VARCHAR(50),@StartRecord) + ' and rowid<= ' + CONVERT(VARCHAR(50),@EndRecord)
Exec(@sql)



--SELECT @totalRecord AS 'fldtotalRecord',@totalPage AS 'fldTotalPage'
select @PageIndex AS PageIndex, @PageSize AS PageSize, @totalRecord AS TotalCount, @TotalPage AS TotalPage
end

[小时候很凶_tusiji]

模仿csdn 发贴模块的存储过程

/*****************************************************************
  发帖
******************************************************************/
alter proc sendtopics
@sessionid varchar(60), 板块ID
@username varchar(30),  用户名
@topic varchar(255),    主体
@topictext varchar(8000), 发贴的正文
@islight int , 是否使用发光字
@istop int,  是否置顶
@ismessage int,  是否是公告信息
@addscore int   此贴子的分数
as
begin
    declare @return int--用于记录返回值
	declare @userid varchar(80)--用户ID     
    select @userid = id from users where username =@username 根据用户名获得用户ID


 if exists (select * from blacklist 
	where userid=@userid and Sessionid=@sessionid
	and datediff(dd,closeTime ,getdate())<=closeDays )
    set @return = -1    //判断在此板块中次用户是否处在小黑屋 是则返回-1
 else
  begin
        if not exists(select * from SendTopic
		where  Userid=@userid and datediff(dd,SendTime,getdate())=0)
         update users set userintegral =userintegral +30  where id=@userid
          insert into sendtopic values(newid(),@sessionid,@userid,@topic,@topictext,0,@ismessage,@istop,@addscore,0,getdate(),@userid,getdate(),0,0,@islight)  
                             判断今天该用户是否发过帖子   如果未发过帖子 加30分 并插入数据库

          update users set userintegral = userintegral-@addscore where id=@userid
          if not exists(select * from UserSortPoint where userid =@userid and sessionid =@sessionid)
             insert into UserSortPoint values (newid(),@userid,@sessionid,0) 然后 在减去该帖子的分数
         if(@islight= 1)  在判断是否使用了发光字
           update users set userintegral = userintegral-10 where id=@userid
          set @return = 1
  end        
   select @return   根据return的结果判断该用户是否处在小黑屋中
  end

[人生就是赌]

提供一个sqlserver2005的分页方法

CREATE PROCEDURE [dbo].[Pagination]
@TotalCount INT OUTPUT,
@TotalPage INT OUTPUT,
@Table NVARCHAR(1000), --将要查询的表名
@Column NVARCHAR(1000),--将要查询的字段，可多列
@OrderColumn NVARCHAR(100), --排序字段
@GroupColumn NVARCHAR(150),--分组字段
@PageSize INT,--分页大小
@CurrentPage INT, --要查询的页
@Group tinyint,--是否使用分组，0否1是
@Condition NVARCHAR(4000) --查询条件
AS
DECLARE @PageCount INT,--总页数
@strSql NVARCHAR(4000), --主查询语句
@strTemp NVARCHAR(2000), --临时变量
@strCount NVARCHAR(1000),--统计语句
@strOrderType NVARCHAR(1000) --排序语句
BEGIN
SET @PageCount=@PageSize*(@CurrentPage-1)
SET @strOrderType=' ORDER BY ' + @OrderColumn +' '
IF @Condition!=''
	BEGIN
		IF @CurrentPage=1
		BEGIN
			IF @GROUP=1
			BEGIN
				SET @strCount='SELECT  @TotalCount=COUNT(*) FROM '+ @Table+ ' WHERE '+@Condition+' GROUP BY '+@GroupColumn
				SET @strCount=@strCount+' SET @TotalCount=@@ROWCOUNT'
				SET @strSql='SELECT TOP '+ str(@PageSize) +' '+ @Column +' FROM '+ @Table + ' WHERE ' + @Condition+' GROUP BY '+@GroupColumn + ' '+@strOrderType
			END
			ELSE
			BEGIN
				SET @strCount='SELECT  @TotalCount=COUNT(*) FROM '+ @Table+ ' WHERE '+@Condition
				SET @strSql='SELECT TOP '+ str(@PageSize) +' '+ @Column +' FROM '+ @Table + ' WHERE ' + @Condition + ' '+@strOrderType
			END
		END
		ELSE
		BEGIN
			IF @GROUP=1
			BEGIN
				SET @strCount='SELECT  @TotalCount=COUNT(*) FROM '+ @Table+ ' WHERE '+@Condition+' GROUP BY '+@GroupColumn
				SET @strCount=@strCount+' SET @TotalCount=@@ROWCOUNT'
				SET @strSql='SELECT  * FROM (SELECT TOP (2000)  '+ @Column +',ROW_NUMBER() OVER('+@strOrderType+') AS NUM FROM '+@Table+' WHERE '+@Condition+' GROUP BY '+@GroupColumn+') AS T WHERE NUM BETWEEN '+str(@PageCount+1)+ ' AND '+str(@PageCount+@PageSize) 
			END
			ELSE
			BEGIN
				SET @strCount='SELECT  @TotalCount=COUNT(*) FROM '+ @Table+ ' WHERE '+@Condition
				SET @strSql='SELECT  * FROM (SELECT TOP (2000)  '+ @Column +',ROW_NUMBER() OVER('+@strOrderType+') AS NUM FROM '+@Table+' WHERE '+@Condition+') AS T WHERE NUM BETWEEN '+str(@PageCount+1)+ ' AND '+str(@PageCount+@PageSize)
			END
		END
	END
ELSE--没有查询条件
BEGIN
	IF @CurrentPage=1
	BEGIN
		IF @GROUP=1
		BEGIN
			SET @strCount='SELECT  @TotalCount=COUNT(*) FROM '+ @Table+' GROUP BY '+@GroupColumn
			SET @strCount=@strCount+'SET @TotalCount=@@ROWCOUNT'
			SET @strSql='SELECT TOP '+ str(@PageSize) +' '+ @Column +' FROM '+ @Table+' GROUP BY '+@GroupColumn +' '+@strOrderType
		END
		ELSE
		BEGIN
			SET @strCount='SELECT  @TotalCount=COUNT(*) FROM '+ @Table
			SET @strSql='SELECT TOP '+ str(@PageSize) +' '+ @Column +' FROM '+ @Table +' '+@strOrderType
		END
	END
	ELSE
	BEGIN
		IF @GROUP=1
		BEGIN
			SET @strCount='SELECT  @TotalCount=COUNT(*) FROM '+ @Table+' GROUP BY '+@GroupColumn
			SET @strCount=@strCount+'SET @TotalCount=@@ROWCOUNT'
			SET @strSql='SELECT  * FROM (SELECT TOP (2000)  '+ @Column +',ROW_NUMBER() OVER('+@strOrderType+') AS NUM FROM '+@Table+' GROUP BY '+@GroupColumn+') AS T WHERE NUM BETWEEN '+STR(@PageCount+1)+ ' AND '+STR(@PageCount+@PageSize)
		END
		ELSE
		BEGIN
			SET @strCount='SELECT  @TotalCount=COUNT(*) FROM '+ @Table
			SET @strSql='SELECT  * FROM (SELECT TOP (2000)  '+ @Column +',ROW_NUMBER() OVER('+@strOrderType+') AS NUM FROM '+@Table+') AS T WHERE NUM BETWEEN '+STR(@PageCount+1)+ ' AND '+STR(@PageCount+@PageSize)
		END
	END
END
EXEC sp_executesql @strCount,N'@TotalCount INT OUTPUT',@TotalCount OUTPUT
IF @TotalCount>2000
BEGIN
	SET @TotalCount=2000
END
IF @TotalCount%@PageSize=0
BEGIN
	SET @TotalPage=@TotalCount/@PageSize
END
ELSE
BEGIN
	SET @TotalPage=@TotalCount/@PageSize+1
END
SET NOCOUNT ON
EXEC (@strSql)
END
SET NOCOUNT OFF

其中的 @Table NVARCHAR(1000), --将要查询的表名
可以是不只是一个表，例如 a inner join b on a.id=b.id
如果是多表查询，那么列就必须带上表名 例如： a.id,a.username,b.sex,b.bid

[zzxap]

[code=SQL]
给个简单的你
create proc xx
(
@name varchar(100),
@id int
)
as
declare @sql varchar(5000)
set @sql='select a.*,b.* from table1 a left join table2 b on a.id=b.id where a.name=@name and a.id=@id '
exec @sql
go

调用 exec xx 'aa','11'
[/CODE]

[jhdxhj]

ding

[delphi_new]

天啊，乱花花的

[jackwuzhidong]

千万数量级分页存储过程

效果演示：http://www.cn5135.com/_App/Enterprise/QueryResult.aspx

CREATE PROCEDURE CN5135_SP_Pagination
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句，不带Where
7.Group :Group语句,不带Group By

***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)

AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO

[QingFengDaoZhang]

我的做法是把多表查询到的结果放到一个临时表中
再对临时表进行分页

[llsen]

[Quote=引用 4 楼 guochunyang 的回复:]
来源：http://hi.baidu.com/grrc/blog/item/da5d54c63587561c9c163d46.html
--通用分页存储过程
alter PROCEDURE [dbo].[sp_GetRecordFromPage]
@TableName varchar(350), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页…
[/Quote]

然后建立视图，表名传视图名进去

[lovezx1028]

niu xx