CREATE FUNCTION f_sy_dpm_get_linkmanep(
@id varchar(32)
)
RETURNS varchar(2000)
AS
BEGIN
DECLARE @eid varchar(8000)--企业ID
DECLARE @returnvalue varchar(8000)--返回值
select @eid=enterprise_id from DPM_KH_LinkMan a
where a.linkman_id=@id
select @returnvalue=b.enterprise_name from DPM_KH_Enterprises b
where b.enterprise_id =@eid
Declare curEid Cursor
for Select enterprise_id From DPM_KH_LinkMan WHERE linkman_id_tag=@id
--打开游标
Open curEid
--循环并提取记录
Fetch Next From curEid Into @eid
While ( @@Fetch_Status=0 )
begin
select @returnvalue=@returnvalue+','+enterprise_name
from DPM_KH_Enterprises
where enterprise_id=@eid
end
--关闭游标
Close curEid
--释放游标
Deallocate curEid
RETURN STUFF(@returnvalue, 1, 0, '')
END参数值为空的时候 执行一下就出来了 也是null
但我传了一个正确的id值进去就一直不停的执行 这是为什么啊?
@id varchar(32)
)
RETURNS varchar(2000)
AS
BEGIN
DECLARE @eid varchar(8000)--企业ID
DECLARE @returnvalue varchar(8000)--返回值
select @eid=enterprise_id from DPM_KH_LinkMan a
where a.linkman_id=@id
select @returnvalue=b.enterprise_name from DPM_KH_Enterprises b
where b.enterprise_id =@eid
Declare curEid Cursor
for Select enterprise_id From DPM_KH_LinkMan WHERE linkman_id_tag=@id
--打开游标
Open curEid
--循环并提取记录
Fetch Next From curEid Into @eid
While ( @@Fetch_Status=0 )
begin
select @returnvalue=@returnvalue+','+enterprise_name
from DPM_KH_Enterprises
where enterprise_id=@eid
end
--关闭游标
Close curEid
--释放游标
Deallocate curEid
RETURN STUFF(@returnvalue, 1, 0, '')
END参数值为空的时候 执行一下就出来了 也是null
但我传了一个正确的id值进去就一直不停的执行 这是为什么啊?
where b.enterprise_id =@eid
@eid 没有初始化
Open curEid
--循环并提取记录
Fetch Next From curEid Into @eid
While ( @@Fetch_Status=0 )
begin
select @returnvalue=@returnvalue+','+enterprise_name
from DPM_KH_Enterprises
where enterprise_id=@eid
Fetch Next From curEid Into @eid
end
--关闭游标
Close curEid
--释放游标
Deallocate curEid
循环体中还需要一个取值操作,是游标移动!
While ( @@Fetch_Status=0 )
begin
select @returnvalue=@returnvalue+','+enterprise_name
from DPM_KH_Enterprises
where enterprise_id=@eid Fetch Next From curEid Into @eid
end
体内还要加一条循环读取的语句
where a.linkman_id=@id
select @returnvalue=b.enterprise_name from DPM_KH_Enterprises b
where b.enterprise_id =@eid@eid 不是根据
select @eid=enterprise_id from DPM_KH_LinkMan a
where a.linkman_id=@id
查出来了吗?