用以下存储过程，我查到了相应表modules下的字段id，name并且实现了分页，我从网上下的。但是我想再根据表modules的字段id查询另一个表host相应id下的所有字段。
简单的说，我的表modules存放的是板块名称，表host存放的版主，我想查询相应板块的版主，怎么修改一下存储过程来实现呢？求各位帮帮我
- SQL code
IF @currentPage = 1 --第一页 BEGIN IF @condition != '' SET @strSql ='SELECT COUNT(*) FROM [email protected]+ ' WHERE '+ @condition + ' SELECT TOP '+STR(@pageSize)+' [email protected]+' FROM [email protected]+ ' WHERE [email protected][email protected] ELSE SET @strSql ='SELECT COUNT(*) FROM [email protected]+' SELECT TOP '+STR(@pageSize)+' [email protected]+' FROM [email protected][email protected] END ELSE -- 其他页 BEGIN IF @condition !='' SET @strSql ='SELECT COUNT(*) FROM [email protected]+ ' WHERE [email protected]+' SELECT TOP '+STR(@pageSize)+' [email protected]+' FROM [email protected]+ ' WHERE [email protected]+' AND [email protected][email protected]+'([email protected]+')'+' FROM (SELECT TOP '+STR((@currentPage-1)[email protected])+ ' [email protected]+' FROM [email protected] + ' where [email protected][email protected]+') AS TabTemp)[email protected] ELSE SET @strSql ='SELECT COUNT(*) FROM [email protected]+ ' SELECT TOP '+STR(@pageSize)+' [email protected]+' FROM [email protected]+ ' WHERE [email protected][email protected]+'([email protected]+')'+' FROM (SELECT TOP '+STR((@currentPage-1)[email protected])+' [email protected]+ ' FROM [email protected][email protected]+') AS TabTemp)[email protected]
------解决方案--------------------
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 [email protected]+' where [email protected]
set @string=' and [email protected]
end
else
begin
set @temp=N'select @c=COUNT(*) from [email protected]
end
if(@PageIndex!=1)
begin
set @sql=N'select top '+CONVERT(nvarchar,@PageSize)+' [email protected]+' from [email protected]+' where '
[email protected]+' [email protected]+'(select max([email protected]+')-1 from (select top '+CONVERT(nvarchar,@PageSize*(@PageIndex-1))
+' [email protected]+' from [email protected]+' where 1=1 [email protected]+' order by [email protected]+' [email protected]+') as sun1)[email protected]+' order by [email protected]+' [email protected]
end
else
begin
set @sql=N'select top '+CONVERT(nvarchar,@PageSize)+' [email protected]+' from [email protected]+' where [email protected]+' order by [email protected]+' [email protected]
end
exec sp_executesql @temp,[email protected] int output',@TolRecord output
set @TolPage=CEILING(@[email protected])
exec(@sql)