关闭

sql procedure 更新案例

618人阅读 评论(0) 收藏 举报

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:4610次
    • 积分:108
    • 等级:
    • 排名:千里之外
    • 原创:4篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章存档