说明：在supplyplan表中，存储着每一个requestqty及其对应的开始终止日期段；因为我在以后处理中要判断当前天属于哪一条requestqty的日期区间并进行处理，所以后台数据库只能设计成这种存储形式；但是在页面的显示时候，需要动态的根据每一个supplyplanno生成对应的多条日期区段及其数量显示，所以采用自定义函数形式返回处理以后的动态记录集！返回的字段@allrequestqty为"1|13|5|8"形式，在asp中就可以采用split函数分组从而动态显示字段。
/*
** add by wls for: convert many lines of sp datas to one char group by vehicle,part
*/
create function xzc.fn_getcontent_supplyplan
(@s_p_no int = null,
@viewflag varchar(1) = ''
)
returns @fn_getcontent_supplyplan table
(
id varchar(8000),
supplyplanno int,
vehicle varchar(25),
sort varchar(20),
part varchar(25),
requestqty varchar(8000),
remark varchar(255)
)
as
begin
declare @svehicle varchar(25), @ssort varchar(20)
declare @spart varchar(25), @ivehicle varchar(25)
declare @ipart varchar(25), @sremark varchar(255)
declare @requestqty int , @theid int
declare @allrequestqty varchar(8000), @allid varchar(8000)
select @allrequestqty = ''
select @allid = ''
/*outer cursor*/
declare getvehiclepartsp cursor scroll for
select distinct vehicle, sort, part from supplyplan
where supplyplanno = @s_p_no and viewflag = @viewflag
/* --order by id desc
select vehicle, sort, part from (select min(id) as id, vehicle, sort, part from supplyplan
where supplyplanno = @s_p_no group by vehicle, sort, part) a order by a.id desc
*/
/*inner cursor*/
declare getcontentsp cursor scroll for
select id, vehicle, part, requestqty
from supplyplan where supplyplanno = @s_p_no and viewflag = @viewflag order by id
if @s_p_no is null or @s_p_no = ''
begin
return
end
open getvehiclepartsp
fetch first from getvehiclepartsp into @svehicle,@ssort,@spart
while(@@fetch_status = 0)
begin
open getcontentsp
fetch first from getcontentsp into @theid, @ivehicle, @ipart, @requestqty
while(@@fetch_status = 0)
begin
if @ivehicle = @svehicle and @ipart = @spart
begin
select @allid = @allid + '|' + convert(varchar(15), @theid)
select @allrequestqty = @allrequestqty + '|' + convert(varchar(25), @requestqty)
end
fetch next from getcontentsp into @theid, @ivehicle, @ipart, @requestqty
end
close getcontentsp
select @allid = xzc.trim_char(@allid, '|')
select @allrequestqty = xzc.trim_char(@allrequestqty, '|')
select @sremark = (select top 1 remark from supplyplan where supplyplanno = @s_p_no
and vehicle = @svehicle and part = @spart)
insert into @fn_getcontent_supplyplan values(@allid, @s_p_no, @svehicle, @ssort, @spart, @allrequestqty, @sremark)
