新手请教 存储过程优化

[TheGodOfGods]

有如下两张表

create table tb_ClientGroup(GroupID varchar(100), GroupPath varchar(3700), GroupName varchar(1000))
insert into tb_ClientGroup values('000000000001', '', 'RootGroup')
insert into tb_ClientGroup values('000000000002', '000000000001', 'ChildOne')
insert into tb_ClientGroup values('000000000003', '000000000001', 'ChildTwo')
insert into tb_ClientGroup values('000000000004', '000000000001', 'ChildThree')
insert into tb_ClientGroup values('000000000005', '000000000001\000000000002', 'grandChildOne')
insert into tb_ClientGroup values('000000000006', '000000000001\000000000002', 'grandChildTwo')
--ChildOne，ChildTwo为RootGroup子组，grandChildOne和grandChildTwo为ChildOne子组（从GroupPath分析出的结果）

create table tb_GroupRole(GroupID varchar(100),RoleType int)
insert into tb_Grouprole values('000000000001', 0)
insert into tb_Grouprole values('000000000002', 1)
insert into tb_Grouprole values('000000000003', 1)
insert into tb_Grouprole values('000000000004', 2)
insert into tb_Grouprole values('000000000005', 3)
insert into tb_Grouprole values('000000000006', 3)
--tb_GroupRole.GroupID与tb_ClientGroup.GroupID相对应

想有一存储过程，由tb_GroupRole.RoleType查询tb_ClientGroup表中对应所有该GroupID的子组和其自身 的tb_ClientGroup.GroupID
比如RoleType为0,应该返回所有列；RoleType为1,返回000000000002，000000000003，000000000005，000000000006；RoleType为2,返回000000000004，
而且若附加GroupName判断，也须满足该判断条件

下边是我写的存储过程及测试结果，想请教各位大侠，是否有更好方法来实现或者优化这存储过程？这个感觉总太生硬了:(

create procedure dbo.GetGroupID
	@GroupRole int,
	@GroupName varchar(1000) = NULL
AS
DECLARE @strPath varchar(3700),
	@strID varchar(100)
BEGIN
	create table #tmpGroupID(GroupID varchar(100))
	create table #tmpGroupIDAndPath(GroupID varchar(100), GroupPath varchar(3700))

--先把满足@GroupRole条件的所有数据存入#tmpGroupIDAndPath表
	insert into #tmpGroupIDAndPath select GroupID,GroupPath from tb_ClientGroup where (tb_ClientGroup.GroupID in(
		select GroupID from tb_GroupRole where tb_GroupRole.RoleType=@GroupRole
	    	))
	
--按#tmpGroupIDAndPath表中GroupID值（该值具有唯一性）依逐条将满足要求的数据插入临时表#tmpGroupID
	while (select count(*) from #tmpGroupIDAndPath)>0
	BEGIN
		select Top 1 @strID=GroupID,@strPath=GroupPath from #tmpGroupIDAndPath
		--重新组合GroupPath条件
		if len(@strPath) > 0
			SET @strPath = @strPath + '\'+ @strID + '%'
		else SET @strPath =  @strID + '%'
		insert into #tmpGroupID select GroupID from tb_ClientGroup where 
			(@GroupName IS NULL or tb_ClientGroup.GroupName  like @GroupName)
			AND (
			(tb_ClientGroup.GroupPath like @strPath)
			OR (tb_ClientGroup.GroupID = @strID)
			)
		delete from #tmpGroupIDAndPath where GroupID = @strID
	END

--显示结果
	select * from #tmpGroupID order by GroupID
	DROP table #tmpGroupIDAndPath
	drop table #tmpGroupID
END

--测试结果
exec dbo.GetGroupID
	@GroupRole = 0
--GroupID
--000000000001
--000000000002
--000000000003
--000000000004
--000000000005
--000000000006

exec dbo.GetGroupID
	@GroupRole = 1
--000000000002
--000000000003
--000000000005
--000000000006

exec dbo.GetGroupID
	@GroupRole = 2
--000000000004

drop table tb_Grouprole
drop table tb_ClientGroup

[chuifengde]

??
create proc dbo.GetGroupID
    @GroupRole int,
    @GroupName varchar(1000) = NULL
AS
    select  a.GroupID from tb_ClientGroup a ,tb_GroupRole b 
    where b.RoleType=@GroupRole and charindex('\'+b.GroupID+'\','\'+GroupPath+'\')>0
    and GroupName=isnull(@GroupName,GroupName)

[chuifengde]

改一下：

ALTER  proc dbo.GetGroupID
    @GroupRole int,
    @GroupName varchar(1000) = NULL
AS
    SELECT GroupId FROM tb_GroupRole WHERE RoleType=@GroupRole
    UNION ALL 
    select  a.GroupID from tb_ClientGroup a ,tb_GroupRole b 
    where b.RoleType=@GroupRole and charindex('\'+b.GroupID+'\','\'+GroupPath+'\')>0
    and GroupName=isnull(@GroupName,GroupName)
    

[东那个升]

create procedure dbo.GetGroupID
    @GroupRole int
as
	select GroupID from tb_GroupRole where RoleType=@GroupRole
	
	union all
	
	select GroupID from tb_ClientGroup  a
	where exists (select 1 from tb_GroupRole
				  where '\'+GroupPath+'\' like  '%\'+GroupID+'\%' and  RoleType=@GroupRole)
go


exec GetGroupID    @GroupRole = 0
/*
GroupID
-----------------------
000000000001
000000000002
000000000003
000000000004
000000000005
000000000006

(6 行受影响)
*/
exec GetGroupID    @GroupRole = 1
/*
GroupID
-------------------
000000000002
000000000003
000000000005
000000000006

(4 行受影响)
*/

exec GetGroupID    @GroupRole = 2
/*
GroupID
--------------
000000000004

(1 行受影响)
*/

[njlywy]

up…

[TheGodOfGods]

[Quote=引用 2 楼 chuifengde 的回复:]

改一下：
SQL code
ALTER proc dbo.GetGroupID
@GroupRole int,
@GroupName varchar(1000) = NULL
AS
SELECT GroupId FROM tb_GroupRole WHERE RoleType=@GroupRole
UNION ALL
select a.Group……
[/Quote]
多谢~！
不过我并不喜欢用 isnull 我觉得要是改成

ALTER  proc dbo.GetGroupID
    @GroupRole int,
    @GroupName varchar(1000) = NULL
AS
    SELECT GroupId FROM tb_GroupRole WHERE RoleType=@GroupRole
    UNION ALL 
    select  a.GroupID from tb_ClientGroup a ,tb_GroupRole b 
    where b.RoleType=@GroupRole and charindex('\'+b.GroupID+'\','\'+GroupPath+'\')>0
    and (@GroupName IS NULL or a.GroupName=@GroupName)

会不会更好点？

[TheGodOfGods]

[Quote=引用 2 楼 chuifengde 的回复:]

改一下：
SQL code
ALTER proc dbo.GetGroupID
@GroupRole int,
@GroupName varchar(1000) = NULL
AS
SELECT GroupId FROM tb_GroupRole WHERE RoleType=@GroupRole
UNION ALL
select a.Group……
[/Quote]
这地方还是有点问题，我想实现下边的效果

exec dbo.GetGroupID
	@GroupRole = 0,
	@GroupName = 'ChildTwo'
/*
--000000000003
*/
--而非 现在的
/*
--000000000001
--000000000003
*/

也就是说‘SELECT GroupId FROM tb_GroupRole WHERE RoleType=@GroupRole’的结果能被union的前提是GroupName=@GroupName（假如@GroupName非空）

[chuifengde]

ALTER  proc dbo.GetGroupID
    @GroupRole int,
    @GroupName varchar(1000) = NULL
AS
    select  a.GroupID from tb_ClientGroup a ,tb_GroupRole b 
    where b.RoleType=@GroupRole and GroupName=isnull(@GroupName,GroupName) and (charindex('\'+b.GroupID+'\','\'+GroupPath+'\')>0 OR a.GroupID=b.GroupID) 
    

[TheGodOfGods]

[Quote=引用 7 楼 chuifengde 的回复:]

SQL code
ALTER proc dbo.GetGroupID
@GroupRole int,
@GroupName varchar(1000) = NULL
AS
select a.GroupID from tb_ClientGroup a ,tb_GroupRole b
where b.RoleType=@GroupRole and GroupN……
[/Quote]
学习了~！

[htl258_Tony]

来迟了，帮顶。

[yjwcwrkks]

帮顶，。。