分页

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技术。
--存储过程过程语法，
--临时表