CREATE PROCEDURE proc_GetAllAcceptOrRefuseByProjectNameAndDictNameAndLevel @projectname varchar(200) ='' ,
@seltype varchar(200) ='' ,@level int ='',@selName varchar(200) ='',@dutyman varchar(200) =''
AS
if(@seltype ='全部' and @level =1 and @dutyman = '全部')
begin
SELECT t_project.projectname AS 项目, e.name AS 系统, d .name AS 子系统,
c.name AS 模块, b.name AS 功能, a.name AS 子功能, SubFunctionProperty.id,
SubFunctionProperty.functionexplain AS 功能说明,
SubFunctionProperty.requiretype AS 需求类型,
SubFunctionProperty.requiretime AS 需求时间,
SubFunctionProperty.acceptorrefuse AS 是否接受,
SubFunctionProperty.dutyman as 责任人,
SubFunctionProperty.startforplan as 计划启,
SubFunctionProperty.endforplan as 计划结,
SubFunctionProperty.startforact as 实际启,
SubFunctionProperty.endforact as 实际结
FROM SubFunctionProperty,dict as a,dict as b,dict as c,dict as d,dict as e,t_project
where SubFunctionProperty.parent = a.id and a.parent = b.id and b.parent = c.id and c.parent = d.id
and d.parent = e.id and e.parent = t_project.id and t_project.projectname = @projectname and e.name = @selName
order by e.name,d.name,c.name,b.name,a.name
end else if(@seltype ='全部' and @level =2)
begin
SELECT t_project.projectname AS 项目, e.name AS 系统, d .name AS 子系统,
c.name AS 模块, b.name AS 功能, a.name AS 子功能, SubFunctionProperty.id,
SubFunctionProperty.functionexplain AS 功能说明,
SubFunctionProperty.requiretype AS 需求类型,
SubFunctionProperty.requiretime AS 需求时间,
SubFunctionProperty.acceptorrefuse AS 是否接受,
SubFunctionProperty.dutyman as 责任人,
SubFunctionProperty.startforplan as 计划启,
SubFunctionProperty.endforplan as 计划结,
SubFunctionProperty.startforact as 实际启,
SubFunctionProperty.endforact as 实际结
FROM SubFunctionProperty,dict as a,dict as b,dict as c,dict as d,dict as e,t_project
where SubFunctionProperty.parent = a.id and a.parent = b.id and b.parent = c.id and c.parent = d.id
and d.parent = e.id and e.parent = t_project.id and t_project.projectname = @projectname and d.name = @selName
order by e.name,d.name,c.name,b.name,a.name
end else if(@seltype ='全部' and @level =3)
begin
代码
end else if(@seltype ='全部' and @level =4)
begin
代码
end else if(@seltype <>'全部' and @level = 1)
begin
代码
end else if(@seltype <>'全部' and @level =2)
begin
代码
end else if(@seltype <>'全部' and @level = 3)
begin
代码
end else if(@seltype <>'全部' and @level = 4)
begin
代码
end
GO 我没有全部写出来,因为像@level这样的参数还要继续加入,我不知道怎样才能把这么多重复的少做些,主要是if else放在什么地方可以把它减少些
@seltype varchar(200) ='' ,@level int ='',@selName varchar(200) ='',@dutyman varchar(200) =''
AS
if(@seltype ='全部' and @level =1 and @dutyman = '全部')
begin
SELECT t_project.projectname AS 项目, e.name AS 系统, d .name AS 子系统,
c.name AS 模块, b.name AS 功能, a.name AS 子功能, SubFunctionProperty.id,
SubFunctionProperty.functionexplain AS 功能说明,
SubFunctionProperty.requiretype AS 需求类型,
SubFunctionProperty.requiretime AS 需求时间,
SubFunctionProperty.acceptorrefuse AS 是否接受,
SubFunctionProperty.dutyman as 责任人,
SubFunctionProperty.startforplan as 计划启,
SubFunctionProperty.endforplan as 计划结,
SubFunctionProperty.startforact as 实际启,
SubFunctionProperty.endforact as 实际结
FROM SubFunctionProperty,dict as a,dict as b,dict as c,dict as d,dict as e,t_project
where SubFunctionProperty.parent = a.id and a.parent = b.id and b.parent = c.id and c.parent = d.id
and d.parent = e.id and e.parent = t_project.id and t_project.projectname = @projectname and e.name = @selName
order by e.name,d.name,c.name,b.name,a.name
end else if(@seltype ='全部' and @level =2)
begin
SELECT t_project.projectname AS 项目, e.name AS 系统, d .name AS 子系统,
c.name AS 模块, b.name AS 功能, a.name AS 子功能, SubFunctionProperty.id,
SubFunctionProperty.functionexplain AS 功能说明,
SubFunctionProperty.requiretype AS 需求类型,
SubFunctionProperty.requiretime AS 需求时间,
SubFunctionProperty.acceptorrefuse AS 是否接受,
SubFunctionProperty.dutyman as 责任人,
SubFunctionProperty.startforplan as 计划启,
SubFunctionProperty.endforplan as 计划结,
SubFunctionProperty.startforact as 实际启,
SubFunctionProperty.endforact as 实际结
FROM SubFunctionProperty,dict as a,dict as b,dict as c,dict as d,dict as e,t_project
where SubFunctionProperty.parent = a.id and a.parent = b.id and b.parent = c.id and c.parent = d.id
and d.parent = e.id and e.parent = t_project.id and t_project.projectname = @projectname and d.name = @selName
order by e.name,d.name,c.name,b.name,a.name
end else if(@seltype ='全部' and @level =3)
begin
代码
end else if(@seltype ='全部' and @level =4)
begin
代码
end else if(@seltype <>'全部' and @level = 1)
begin
代码
end else if(@seltype <>'全部' and @level =2)
begin
代码
end else if(@seltype <>'全部' and @level = 3)
begin
代码
end else if(@seltype <>'全部' and @level = 4)
begin
代码
end
GO 我没有全部写出来,因为像@level这样的参数还要继续加入,我不知道怎样才能把这么多重复的少做些,主要是if else放在什么地方可以把它减少些
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货