兩個表的分頁存儲過程

create proc [dbo].[usp_contacts_select_by_page]--存儲過程名稱

@pageindex int,--當前頁

@pagesize int,--每頁條數

@pagecount int output,--總頁數

@recordcount int output--總條數

as

begin

select c1.*,c2.groupName into #tmp_contacts from Contacts as c1 inner join

ContactGroup as c2 on c1.groupId=c2.groupId--將兩個表中的數據存放到臨時表中

select * from

(select *,rn=ROW_NUMBER()over(order by contactId asc)from #tmp_contacts)as t

where t.rn between(@pageindex-1)*@pagesize+1 and @pageindex*@pagesize

set @recordcount=(select COUNT(*)from #tmp_contacts)

set @pagecount=CEILING(@recordcount*1.0/@pagesize)

end

執行

declare @m int,@n int

exec [dbo].[usp_contacts_select_by_page] 2,5,@m output,@n output

print @m

print @n