drop proc GetPageDataBuilding
create proc GetPageDataBuilding
@pageSize int,
@pageIndex int,
@totalCount int output
as
select * into #Temp from --根据中间查询结果表创建了一个临时表
(select B.*,P.ProjectName,U.UName,Br.BranchName from BuildingInfo as B
left join ProjectInfo as P on P.Id=B.ProjectId
left join UserInfo as U on U.Id=P.SubBy
left join Branch as Br on P.BranchId=Br.Id) as T
declare @str nvarchar(1000);
--拼接了sql（2000）
set @str =('select top('+CAST( @pageSize as nvarchar(32))+') * from #Temp where Id not in(select top('+cast((@pageIndex-1)*@pageSize as nvarchar(32))+ ') Id from #Temp order by Id) order by Id')
--拼接了sql（2005）
set @str=（'select * from (select *,row_number() over(order by id) as rn from [表名]) as h where h.rn between @pageSize*(@pageIndex-1)+1 and @pageSize*@pageIndex'）
exec (@str)
print @str
--给输出参数赋值
select @totalCount=COUNT(1) from #Temp;
drop table #Temp;--
declare @count int
exec GetPageDataBuilding 4,1,@count output
print @count
--分页语法，动态拼接sql，并执行sql技术。
--存储过程过程语法，
--临时表
