ALTER function [dbo].[F_GetOrderData]
(
@OrderNO varchar(30)
)
RETURNS @ContentData TABLE
(
OrderNO varchar(20),
type_C_P varchar(200) default ('') --类别,成份,百分比合在一起
)
as
begin
declare @typeTemp_P varchar(200) ,@TypeTemp2 varchar(200)
declare @typeC_P varchar(200)
select @typeTemp_P='null',@TypeTemp2='',@typeC_P=''
declare @curOrderNO varchar(200)
declare @Type varchar(200)
declare @Content varchar(200)
declare @Percentage varchar(200)
declare @rownum int
select @rownum= count(*)from O_OrderContent where [OrderNO]=@OrderNO
if @rownum<0
return
else
declare mycursor cursor for (select [OrderNO],[Type],[Content],[Percentage] from O_OrderContent where [OrderNO]=@OrderNO) --声明游标
open mycursor --打开游标
--从游标里取出数据库赋值到我们刚才声明的2个变量中
fetch next from mycursor into @curOrderNO,@Type,@Content,@Percentage
--判断游标的状态 0 fetch语句成功 -1语句失败 -2不提取的不存在
while(@@fetch_status=0)
begin
if (@Type<>@typeTemp_P)
select @TypeTemp2='|'+@Type+': '
else
select @TypeTemp2=''
set @typeC_P=@typeC_P+convert(nvarchar,@TypeTemp2)+convert(nvarchar,@Percentage+'%'+' ')+convert(nvarchar,@Content+',')
set @typeTemp_P=@Type --赋值给临时变量,以便下次判断
fetch next from mycursor into @curOrderNO,@Type,@Content,@Percentage
end
set @typeC_P=substring(@typeC_P,2,len(@typeC_P)) --把第一个 | 支掉
set @typeC_P=substring(@typeC_P,1,len(@typeC_P)-1) --把最后一个,去掉
set @typeC_P=replace(@typeC_P,',|','|')
insert into @ContentData(OrderNO,type_C_P)values(@OrderNO,@typeC_P)
--关闭游标
close mycursor
--撤销游标
deallocate mycursor
--select * from @ContentData
RETURN
end