sql procedure 更新案例

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Schedule_Unfinished]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Schedule_Unfinished]
GO

CREATE PROC Schedule_Unfinished

AS
declare @sDEPT_CODE  nvarchar(100),
@sCELL nvarchar(100),
@sMOLINEKEY  int,
@sUnfinishQTY float

declare tempObjects cursor for
select NRH_PRODSCH.MOLINEKEY,DEPT_CODE,CELL,(PLAN_QTY-FinishQTY) as UnfinishQTY from NRH_PRODSCH inner join
 (select MOLINEKEY,sum(PRINT_QTY) FinishQTY,CELL_NO from  NRH_FGREICEPT
 where  POST_DATE BETWEEN
 (select cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-'+cast(day(getdate()-1)as varchar)+' 08:00:00')
 and
 (select cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-'+cast(day(getdate())as varchar)+' 08:00:00')
 group by MOLINEKEY,CELL_NO) CC
 on (NRH_PRODSCH.MOLINEKEY=CC.MOLINEKEY AND NRH_PRODSCH.CELL=CC.CELL_NO)
 where PROD_DATE=(select cast(year(getdate()-1) as varchar)+'-'+cast(month(getdate()-1) as varchar)+'-'+cast(day(getdate()-1)as varchar)+' 00:00:00')
 
open tempObjects
FETCH NEXT from tempObjects INTO @sMOLINEKEY,@sDEPT_CODE,@sCELL,@sUnfinishQTY
if (@@FETCH_STATUS = 0)
begin
 update NRH_PRODSCH set LAST_QTY=@sUnfinishQTY
 where PROD_DATE=(select cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-'+cast(day(getdate())as varchar)+' 00:00:00') and DEPT_CODE=@sDEPT_CODE and CELL=@sCELL and MOLINEKEY=@sMOLINEKEY
 FETCH NEXT FROM tempObjects
end
CLOSE tempObjects
DEALLOCATE tempObjects
GO