创建合并字段值函数，运行出现“传递到 RIGHT 函数的长度参数无效”错误

[linton]

有个表XMKJ，有三个值XMMC, XH, KJH，如：
XMMC XH KJH
XM1 1 K1
XM1 2 K2
XM1 3 K3
XM2 1 K4
XM3 1 K5
XM3 2 K6
想实现按照相同字段值的XMMC按XH排序并合并KJH，结果为
XM1 K1;K2;K3
XM2 K4
XM3 K5;K6

上网搜索，发现有人问过这样的问题，用函数方法可以解决

我的函数如下：
create function GetXMKJH(@XMMC varchar)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+';'+KJDJH
from XMKJ
where XMMC=@XMMC
set @str=right(@str,len(@str)-1)
return @str
end


函数创建完成后，运行下面语句
select distinct(xmmc), dbo.GetXMKJH(xmmc) from XMKJ
出现如下错误：
消息 536，级别 16，状态 2，第 1 行
传递到 RIGHT 函数的长度参数无效。

[SQL77]

create  function  GetXMKJH(@XMMC  varchar)  
  returns  varchar(2000)  
  as    
  begin  
  declare  @str  varchar(2000)  

  select  @str=ISNULL(@str+';','')+KJDJH  
    from  XMKJ  
  where  XMMC=@XMMC  
   
  return  @str  
  end 

[SQL77]

create function GetXMKJH(@XMMC varchar(5000))
returns varchar(2000)
as
begin
declare @str varchar(2000)

select @str=ISNULL(@str+';','')+KJDJH
from XMKJ
where XMMC=@XMMC

return @str
end

[bancxc]

create  function  GetXMKJH(@XMMC  varchar)  
  returns  varchar(2000)  
  as    
  begin  
  declare  @str  varchar(2000)  
  set  @str=''  
  select  @str=@str+';'+KJDJH  
    from  XMKJ  
  where  XMMC=@XMMC  
  set  @str=left(@str,len(@str)-1)  
  return  @str  
  end 

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-10 16:03:30
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([XMMC] varchar(3),[XH] int,[KJH] varchar(2))
insert [tb]
select 'XM1',1,'K1' union all
select 'XM1',2,'K2' union all
select 'XM1',3,'K3' union all
select 'XM2',1,'K4' union all
select 'XM3',1,'K5' union all
select 'XM3',2,'K6'
--------------开始查询--------------------------
CREATE FUNCTION dbo.f_strUnite(@id varchar(10)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ':' + KJH FROM tb WHERE XMMC=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
-- 调用函数 
SELECt XMMC, KJH = dbo.f_strUnite(XMMC) FROM tb GROUP BY XMMC 
drop table tb 
drop function dbo.f_strUnite 
go
----------------结果----------------------------
/* XMMC KJH
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
XM1  K1:K2:K3
XM2  K4
XM3  K5:K6

(3 行受影响)

*/

[bancxc]

--不好意思 刚左右不分了
create  function  GetXMKJH(@XMMC  varchar(100))  
  returns  varchar(2000)  
  as    
  begin  
  declare  @str  varchar(2000)  
  set  @str=''  
  select  @str=@str+';'+KJDJH  
    from  XMKJ  
  where  XMMC=@XMMC  
  set  @str=right(@str,len(@str)-1)  
  return  @str  
  end 

[qqqwwwqw]

select distinct(xmmc), dbo.GetXMKJH(xmmc) from XMKJ group by xmmc