郁闷 自定义函数这句代码如何无效呢

郁闷 自定义函数这句代码怎么无效呢

SQL code

CREATE FUNCTION [dbo].[Fn_ShowDepartment](@ID int)RETURNS varchar(500)ASBEGINDeclare @Count intDeclare @Index intDeclare @Department varchar(200)Declare @Mark varchar(500)set @Count = (select count(*) from OA_GongGao_Detail where GGID = @ID)set @Index=0set @Department=''set @Mark=''while @Index < @Countbeginset @Index = @Index+1select @Department =(select top 1 DepartmentName from(select top(@Index) GG.ID as ID,DT.DepartmentName as DepartmentName,GG.ToDepartmentId as ToDepartmentId,GGID as GGID from OA_GongGao_Detail GG left join Sys_Department DT on GG.ToDepartmentId = DT.id where GG.GGID=@ID order by GG.ID) a  order by ID desc)[color=#FF0000]set @Mark = @Mark+@Department+','[/color]endif len(@Mark)>0beginset @Mark = left(@Mark,len(@Mark)-1)endreturn @MarkEND

set @Mark = @Mark+@Department+',' 这句怎么没用呢？？？

[解决办法]
set @Count = (select count(*) from OA_GongGao_Detail where GGID = @ID)这条没有取到值
或者
select @Department =(select top 1 DepartmentName from
(
select top(@Index) GG.ID as ID,DT.DepartmentName as DepartmentName,GG.ToDepartmentId as ToDepartmentId,GGID as GGID 
from OA_GongGao_Detail GG left join Sys_Department DT 
on GG.ToDepartmentId = DT.id where GG.GGID=@ID order by GG.ID
) a 
 order by ID desc) 没有取到值吧
因为我把这两条语的值赋为常量，会有结果出来的。

[解决办法]
在set @Mark = @Mark+@Department+','[/color]
语句前面加上
print @Mark 
print @Department 
一下，看看是否有问题！。
[解决办法]

SQL code

Declare @Department varchar(200)Declare @Mark varchar(500)set @Mark=''set @Department=''select @Department='设计部'set @Mark = @Mark+@Department+','print @Mark
[解决办法]
這樣也可以,
SQL code
 set @Mark = @Mark + case when @Department is null then '' else @Department+',' end 

 

查看更多 下一篇