求助:FETCH NEXT失败,FETCH状态始终都是-1
USE [MyBPCS]
GO
/****** Object: UserDefinedFunction [dbo].[ItmAging] Script Date: 2015/7/9 11:47:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER Function [dbo].[ItmAging](
@wPROD char(35),
@wFac char(3),
@wSCST decimal(15, 5),
@wQONH decimal(11, 3),
@gPD1 int,
@gPD2 int,
@gPD3 int,
@gPD4 int,
@gPD5 int)
returns @tmTable table(
tmQTY1 decimal(11, 3),
tmQTY2 decimal(11, 3),
tmQTY3 decimal(11, 3),
tmQTY4 decimal(11, 3),
tmQTY5 decimal(11, 3),
tmQTY6 decimal(11, 3))
as
begin
declare
@wStart decimal(11, 3),
@wQTY decimal(11, 3),
@wQTY1 decimal(11, 3),
@wQTY2 decimal(11, 3),
@wQTY3 decimal(11, 3),
@wQTY4 decimal(11, 3),
@wQTY5 decimal(11, 3),
@wQTY6 decimal(11, 3),
@wRow int,
@sSQL nvarchar(256)
set @wQTY=0
set @wQTY1=0
set @wQTY2=0
set @wQTY3=0
set @wQTY4=0
set @wQTY5=0
set @wQTY6=0
set @wStart=@wQONH
declare @tmith table(TTDTE decimal(8, 0),TQTY decimal(11, 3))
insert into @tmith
SELECT TTDTE,TQTY FROM MyBPCS.dbo.ITH LEFT OUTER JOIN MyBPCS.dbo.IWM ON LWHS=TWHS
WHERE TPROD='''+@wPROD+''' AND WMFAC='''+@wFac+''' AND TTYPE IN ('#','B','R','SR','U') AND TQTY>0
ORDER BY 1 DESC
declare tmCursor cursor local for select * from @tmith
open tmCursor
declare @csrsts smallint
set @csrsts=CURSOR_STATUS('local','tmCursor')
if @csrsts <=0
begin
set @wQTY6=@wQONH
end
else
begin
declare @wTQTY decimal(11, 3),@wTDTE decimal(8, 0)
fetch next from tmCursor into @wTDTE,@wTQTY
while @@FETCH_STATUS=0
begin
If @wTQTY>=@wStart
begin
set @wQTY=@wStart
end
Else
begin
set @wQTY=@wTQTY
end
set @wStart=@wStart-@wTQTY
declare @wDays int,@wDate date
set @wDate=cast(left(char(@wTDTE), 4)+'-'+substring(char(@wTDTE), 5, 2)+'-'+right(char(@wTDTE),2) as date)
set @wDays=datediff("d", @wDate, getdate())
if @wDays<=@gPD1 begin set @wQTY1=@wQTY1+@wQTY end
if @wDays between @gPD1+1 and @gPD2 begin set @wQTY2=@wQTY2+@wQTY end
if @wDays between @gPD2+1 and @gPD3 begin set @wQTY3=@wQTY3+@wQTY end
if @wDays between @gPD3+1 and @gPD4 begin set @wQTY4=@wQTY4+@wQTY end
if @wDays between @gPD4+1 and @gPD5 begin set @wQTY5=@wQTY5+@wQTY end
if @wDays>@gPD5 begin set @wQTY6=@wQTY6+@wQTY end
fetch next from tmCursor into @wTDTE,@wTQTY
end
end
close tmCursor
deallocate tmCursor
insert into @tmTable values(@wQTY1,@wQTY2,@wQTY3,@wQTY4,@wQTY5,@wQTY6)
return
end