在存储过程里创建临时表，往表中直接插入数据

 1 CREATE PROCEDURE [dbo].[Proc_PX_Practice]
 2 @PageSize int, --每页多少个
 3 @CurrPage int --当前页
 4 
 5 AS
 6 begin
 7      create table #temp_table(
 8                               Row_ID int identity(1,1) primary key not null,  --Row_ID 自增长
 9                               ItemGuid varchar(50),       --Guid
10                               PlanGuid varchar(50), 
11                               Name varchar(50),          --名称
12                               ItemType int               --类型
13 
14                               )                              
15                     
16 
17     --计划
18     insert into #temp_table(ItemGuid,PlanGuid,Name,ItemType) 
19     (
20     select RowGuid,RowGuid,PlanName,'0' from PX_plan
21     )
22 
23     --课程
24     insert into #temp_table(ItemGuid,PlanGuid,Name,ItemType) 
25     (
26     select CourseGuid,PlanGuid,CourseName,'1' from PX_PlanCourse where 
27      PlanGuid in (select PlanGuid from #temp_table where ItemType='0')
28      
29     ) 
30 
31     --课件
32     insert into #temp_table(ItemGuid,PlanGuid,Name,ItemType) 
33     (
34     select a.RowGuid,b.PlanGuid,a.Name,'2' from PX_CourseWare a, #temp_table b where 
35       a.CourseGuid=b.ItemGuid and b.ItemType='1'
36     )
37 
38 
39     --output
40    declare @StrSql varchar(max)
41    declare @TopIndex int
42    set @TopIndex = (@CurrPage-1)*@Pagesize
43    set @StrSql='select top '+str(@Pagesize)+' * from #temp_table where  ItemGuid not in (select top '+str(@TopIndex)+' ItemGuid from #temp_table order by Row_ID) order by Row_ID '
44    
45    exec (@StrSql)
46 
47 
48        select * from #temp_table order by ItemType 
49      
50  end
51 
52 
53 
54 
55 
56 
57 
58 GO
View Code