大哥帮忙提高一下函数执行效率，

[hheizi]

--检查对于某个工作是否有列出一个Message，返回0表示不允许，返回1表示允许
CREATE FUNCTION dbo.IsList
(@MessageId decimal,@UserName varchar(50))
returns char(1)

as
begin
declare @ID decimal;
declare @depart varchar(500);--部门
declare @actor varchar(500); --角色
declare @FlowID decimal; --流程类别（低压与低压变更业务）
declare @GZDH varchar(50);
declare @RecordCount decimal;
declare @GDDW varchar(50);
declare @IsUnion varchar(10); --是否审批，如果是审批，就会从YK_SPXX表中去取部门和角色，否则，就会从MESSAGE表中去取角色，从YK_XPXX中去取部门
select @IsUnion =IsUnion ,@GZDH =GZDH,@actor = actors ,@FlowID=FlowID from WF_MESSAGE WHERE ID=@MESSAGEID;

if @IsUnion = '审批'
begin
declare cur cursor for SELECT spbm,spjs from YK_SPXX WHERE MESSAGEID=@MESSAGEID;
open cur;
fetch next from cur into @depart,@actor;
while @@FETCH_STATUS = 0
begin
--下面把部门编码和角色编码编译一下。
select @depart = mc from GY_BMXX WHERE BH=@DEPART;
select @actor = mc from GY_JCXX where BH=@ACTOR;

set @depart = ',' + @depart + ',';
/*
SELECT @RecordCount = count(id) from GY_YHJC WHERE MC=@USERNAME AND JCMC = @actor and STATUS='启用' AND (BMMC = '-1' OR charindex(@depart,',' + BMMC + ',') >0)
if @RecordCount > 0
begin
return '1';
end
*/
--------------------
declare JS cursor for select ID from GY_YHJC where MC = @USERNAME
open JS
fetch next from JS into @ID;
while @@FETCH_STATUS = 0
begin
SELECT @RecordCount = count(id) from GY_YHJC WHERE ID=@ID AND charindex(',' + JCMC + ',',',' + @actor + ',') >0 and STATUS='启用' AND (BMMC = '-1' OR (charindex(@depart,',' + BMMC + ',') >0))
if @RecordCount > 0
return '1';
fetch next from JS into @ID;
end
close JS
DEALLOCATE JS
--------------------
fetch next from cur into @depart,@actor;
end


end
else
begin
if @FlowID=294 or @FlowID=99 or @FlowID=218 or @FlowID=2 or @FlowID=211---//2为新高，211高压杂项 新低的流程99为低压的变更 218为j居民集装业务流程，要验证权限
begin
if @FlowID=294 or @FlowID=99 or @FlowID=218
begin
select @DEPART = MC from GY_BMXX where BH in(select gddw from YK_YHSQXX_GR WHERE GZDH=@GZDH);
end
else if @FlowID=2 or @FlowID=211
begin
select @DEPART = MC from GY_BMXX where BH in(select gddw from YK_YHSQXX WHERE GZDH=@GZDH);
end
--下面验证一下是否有权限
--set @depart = ',' + @depart + ',';

declare JS cursor for select ID from GY_YHJC where MC = @USERNAME
open JS
fetch next from JS into @ID;
while @@FETCH_STATUS = 0
begin
SELECT @RecordCount = count(id) from GY_YHJC WHERE ID=@ID AND charindex(',' + JCMC + ',',',' + @actor + ',') >0 and STATUS='启用' AND (charindex(@depart,',' + BMMC + ',') >0)
--SELECT @RecordCount = count(id) from GY_YHJC WHERE ID=@ID AND charindex(',' + JCMC + ',',',' + @actor + ',') >0 and STATUS='启用'
if @RecordCount > 0
return '1';
fetch next from JS into @ID;
end
close JS
DEALLOCATE JS
end
---//涉及到业扩的用户！一律要通过验证用户权限
---///////////////////////////////////////////////////////////////


---//涉及到业扩的用户！一律要通过验证用户权限
---///////////////////////////////////////////////////////////////



else
begin
--SELECT @DEPART = MC FROM GY_GZD,GY_BMXX WHERE GZDH = @GZDH and GY_GZD.FQBM =cast(GY_BMXX.BH as varchar(20)) ;
--下面验证一下是否有权限
--set @depart = ',' + @depart + ',';

declare JS cursor for select ID from GY_YHJC where MC = @USERNAME
open JS
fetch next from JS into @ID;
while @@FETCH_STATUS = 0
begin
--SELECT @RecordCount = count(id) from GY_YHJC WHERE ID=@ID AND charindex(',' + JCMC + ',',',' + @actor + ',') >0 and STATUS='启用' AND (BMMC = '-1' OR (charindex(@depart,',' + BMMC + ',') >0))
SELECT @RecordCount = count(id) from GY_YHJC WHERE ID=@ID AND charindex(',' + JCMC + ',',',' + @actor + ',') >0 and STATUS='启用'
if @RecordCount > 0
return '1';
fetch next from JS into @ID;
end
close JS
DEALLOCATE JS
end
end
return '0';
end

[mathe]

数据库我不熟悉，还是问问其他熟悉的人吧

[dobear_0922]

CREATE FUNCTION dbo.IsList(@MessageId   decimal, @UserName   varchar(50)) 
returns   char(1) as 
begin 
	declare   @ID   decimal 
	declare   @depart   varchar(500)--部门 
	declare   @actor   varchar(500)   --角色 
	declare   @FlowID   decimal   --流程类别（低压与低压变更业务） 
	declare   @GZDH   varchar(50) 
	declare   @RecordCount   decimal 
	declare   @GDDW   varchar(50) 
	declare   @IsUnion   varchar(10)	--是否审批，是就从YK_SPXX表中去取部门和角色，
										--否则从MESSAGE表中去取角色，从YK_XPXX中去取部门 

	select @IsUnion=IsUnion, @GZDH=GZDH, @actor=actors, @FlowID=FlowID   
	from   WF_MESSAGE   WHERE   ID=@MESSAGEID; 

	if   @IsUnion='审批'  
	begin 
		if exists
		(
			select GY.id
			from YK_SPXX YS join GY_BMXX GB1 on YS.spbm=GB1.BH
					join GY_BMXX GB2 on YS.spjs=GB2.BH
					join GY_YHJC GY on charindex(','+GY.JCMC+',', ','+GB2.mc+',')>0			
			where YS.MESSAGEID=@MESSAGEID 
				and GY.STATUS='启用'
				and GY.ID in (select ID from GY_YHJC where MC=@USERNAME)
				AND (GY.BMMC='-1' OR charindex(','+GB1.mc+',',','+GY.BMMC+',')> 0)			
		)
			return   '1'	
	end   
	else --@IsUnion<>'审批'
	begin ---//2为新高，211高压杂项   新低的流程99为低压的变更   218为j居民集装业务流程，要验证权限  
		if @FlowID in (294, 99, 218, 2, 211) 
		begin 
			if @FlowID in (294, 99, 218)			   
				select   @DEPART   =   MC   from   GY_BMXX   
				where BH in (select gddw from YK_YHSQXX_GR WHERE GZDH=@GZDH)     
			else -- @FlowID=2     or     @FlowID=211			 
				select   @DEPART   =   MC   from   GY_BMXX   
				where BH in (select gddw from YK_YHSQXX WHERE GZDH=@GZDH)     
			
			if exists
			(
				select GY.id
				from GY_YHJC GY 			
				where charindex(','+GY.JCMC+',', ','+@actor+',')>0 
					and GY.STATUS='启用'
					and GY.ID in (select ID from GY_YHJC where MC=@USERNAME)
					AND charindex(','+@depart+',',','+GY.BMMC+',')> 0			
			) 
				return   '1'      
		end 
		else -- @FlowID not in (294, 99, 218, 2, 211)
		begin 
			if exists
			(
				select GY.id
				from GY_YHJC GY 			
				where charindex(','+GY.JCMC+',', ','+@actor+',')>0 
					and GY.STATUS='启用'
					and GY.ID in (select ID from GY_YHJC where MC=@USERNAME)
			)            
				return   '1' 			
        end -- end of @FlowID not in (294, 99, 218, 2, 211)    
    end -- end of @IsUnion<>'审批'
	return   '0'
end 

[dobear_0922]

去掉了所有的游标，效率应该会高很多，，，