各位高手注意,封装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 '
declare @sql varchar(8000)
set @sql = '
create view v_tmp
as
select GroupID,TargetGroupID,Per.*
from GroupAccessible GA
Inner join ( '+@subsql+ ')Per on GA.BatchPermissionID = Per.BatchID '
exec (@sql)
go
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)
go
--以后只要调用这个视图就可以了
select * from v_tmp
go
drop table [Function],GroupAccessible,BatchPermission
drop view v_tmp
