各位高手注意,封装sql语句的问题.
创建操作环境:
Create   Table   #Function
(
  id   int,
  name   varchar(20)
)
Create   Table   #GroupAccessible
(
  GroupID   int,
  TargetGroupID   int,
  BatchPermissionID   int
)
create   Table   #BatchPermission
(
  BatchID   int,    
  FunctionID   int,
  Editable   bit
)
insert   into     #Function   values(1, 'CompanyName ')
insert   into     #Function   values(2, 'Fax   No ')
insert   into     #Function   values(3, 'FatherName ')
insert   into     #Function   values(4, 'MotherName ')
insert   into     #BatchPermission   values(1,1,1)
insert   into     #BatchPermission   values(1,2,1)
insert   into     #BatchPermission   values(1,3,1)
insert   into     #BatchPermission   values(2,1,1)
insert   into     #BatchPermission   values(2,2,1)
insert   into     #BatchPermission   values(2,3,0)
insert   into     #BatchPermission   values(2,4,0)
insert   into     #GroupAccessible   values(1,2,1)
insert   into     #GroupAccessible   values(1,250,2)


下面这一段sql会返回一个结果集，其他table会join这个结果集，
由于用的非常频繁,请问如果把这一段封装起来，不需要每次使用的时候都重写一次?
想过用存储过程封装起来但是返回的结果集不能直接在select语句中join,请问有没有其他好办法?

注：是用的sqlserver2000，所以用不了2005中的PIVOT/UNPIVOT

declare   @subsql   varchar(4000)
set   @subsql   =   'select   BatchID '
select   @subsql   =   @subsql   +   ',sum(case   FunctionID   when   '+Convert(varchar(2),FunctionID)+ '   then   Convert(int,Editable)   end)   [ '+Convert(varchar(4),FunctionID)+ '] '
from   (select   distinct   FunctionID   from   #BatchPermission)   as   a
set   @subsql   =   @subsql+ '   from   #BatchPermission   group   by   BatchID '

declare   @sql   varchar(8000)
set   @sql   = '
select   GroupID,TargetGroupID,Per.*   from   #GroupAccessible   GA  
Inner   join  
( '+@subsql+ '
)Per   on   GA.BatchPermissionID   =   Per.BatchID '

exec   (@sql)

------解决方案--------------------
--用触发器和视图解决

Create Table [Function]
(
id int,
name varchar(20)
)
Create Table GroupAccessible
(
GroupID int,
TargetGroupID int,
BatchPermissionID int
)
create Table BatchPermission
(
BatchID int,
FunctionID int,
Editable bit
)
go
create trigger tr_BatchPermission on BatchPermission
for delete,update,insert
as
if exists(select 1 from sysobjects where name = 'v_tmp ' and xtype = 'v ')
drop view v_tmp
declare @subsql varchar(4000)
set @subsql = 'select BatchID '
select @subsql = @subsql + ',sum(case FunctionID when '+Convert(varchar(2),FunctionID)+ ' then Convert(int,Editable) end) [ '+Convert(varchar(4),FunctionID)+ '] '
from (select distinct FunctionID from BatchPermission) as a

set @subsql = @subsql+ ' from BatchPermission group by BatchID