请问，这个分页的存储过程怎么查询另一个表的字段呢

请教求救，这个分页的存储过程如何查询另一个表的字段呢？
用以下存储过程，我查到了相应表modules下的字段id，name并且实现了分页，我从网上下的。但是我想再根据表modules的字段id查询另一个表host相应id下的所有字段。
简单的说，我的表modules存放的是板块名称，表host存放的版主，我想查询相应板块的版主，怎么修改一下存储过程来实现呢？求各位帮帮我

SQL code

     IF @currentPage = 1            --第一页         BEGIN           IF @condition != ''             SET @strSql ='SELECT COUNT(*) FROM  '+@tableName+ ' WHERE '+ @condition + ' SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+ ' WHERE '+@condition+@strOrderType           ELSE             SET @strSql ='SELECT COUNT(*) FROM '+@tableName+' SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType         END          ELSE                  -- 其他页         BEGIN           IF @condition !=''             SET @strSql ='SELECT COUNT(*) FROM  '+@tableName+ ' WHERE '+@condition+' SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+            ' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+            ' '+@pkColumn+' FROM '+@tableName + ' where '+@condition+@strOrderType+') AS TabTemp)'+@strOrderType          ELSE            SET @strSql ='SELECT COUNT(*) FROM  '+@tableName+ ' SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+            ' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+             ' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType  

[解决办法]
ALTER proc [dbo].[proc_page]
@PageIndex int,--页索引
@PageSize int=10,--页大小
@Field nvarchar(500),--要查询的字段
@OrderField nvarchar(20),--排序字段
@OrderType nvarchar(10)='asc',--排序方式
@TableName nvarchar(50),--表名
@Where nvarchar(500)='',--条件
@TolRecord int output,--总记录数
@TolPage int output--总页数
as
declare @str nvarchar(10)='>',@temp nvarchar(2000)----查询总记录和页数的sql语句
declare @maxmin nvarchar(4)='min',@string nvarchar(50), @sql nvarchar(2000)----查询列表的sql语句
if(@OrderType<>'asc')
begin
set @str='<'
set @maxmin='max'
end
if(@Where<>'')
begin
set @temp=N'select @c=COUNT(*) from '+@TableName+' where '+@Where
set @string=' and '+@Where
end
else
begin
set @temp=N'select @c=COUNT(*) from '+@TableName
end
if(@PageIndex!=1)
begin
set @sql=N'select top '+CONVERT(nvarchar,@PageSize)+' '+@Field+' from '+@TableName+' where '
+@OrderField+' '+@str+'(select max('+@OrderField+')-1 from (select top '+CONVERT(nvarchar,@PageSize*(@PageIndex-1))
+' '+@OrderField+' from '+@TableName+' where 1=1 '+@string+' order by '+@OrderField+' '+@OrderType+') as sun1)'+@string+' order by '+@OrderField+' '+@OrderType
end
else
begin
set @sql=N'select top '+CONVERT(nvarchar,@PageSize)+' '+@Field+' from '+@TableName+' where '+@Where+' order by '+@OrderField+' '+@OrderType
end
exec sp_executesql @temp,N'@c int output',@TolRecord output
set @TolPage=CEILING(@TolRecord*1.0/@PageSize)
exec(@sql)

查看更多 下一篇