表变量与临时表的使用

这个存储过程帖出来,是因为自己觉得有点价值(自以为是吧)主要含如下技术:
1调运了视图
2.声明了临时表
3.使用了表变量
4.运用了内联接
5.实现了分页
6返回总记录数
CREATE proc [dbo].[p_P]
--输入变量
@Flag int,
@FromAccount nvarchar(50),
@BeginTime datetime,
@EndTime datetime,
@PageIndex int,
@PageSize int
as
--临时表
create table #tmp(
id int,
NumCount int,
SendTime datetime,
Content nvarchar(255)
)
--返出参数
declare @TotalCount int
--声明游标
declare my_cursor cursor scroll
for
--从一个视图里取出唯一值
select distinct InfoID from View_SelectInfoFlag
where FromAccount= @FromAccount
and SendTime<(select DATEADD ( dd , 1, @EndTime )) and SendTime>(select DATEADD ( dd , -1, @BeginTime ))
and Flag=@Flag
open my_cursor
--游标变量
declare @InfoID int
fetch next from my_cursor into @InfoID
while(@@fetch_status=0)
begin
  --声明表变量
DECLARE @TMP2 TABLE(ID INT ,COUNTS INT)
INSERT INTO @TMP2
select MAX(InfoID) AS ID,COUNT(*) as NumCount from dbo.InfoRelation
where InfoID=@InfoID GROUP BY InfoID
--插入临时表,
insert into #tmp
--取值是表变量和一个真实表的内联接记录
  SELECT a.*,b.SendTime,b.content from @TMP2 as a inner JOIN INFO AS B ON a.ID=B.ID
WHERE B.id=@InfoID
fetch next from my_cursor into @InfoID
end
close my_cursor
deallocate my_cursor--删除游标
select @TotalCount=count(*) from #tmp
--分页
select * from(select *,row_number() over(order by SendTime) as rownum from #tmp) as a
where rownum BETWEEN (@PageIndex-1)*(@PageSize+1 )
AND @PageIndex*@PageSize order by SendTime
--删除临时表,而表变量却不用删除
drop table #tmp
--返出值
return @TotalCount