sql procedure 更新案例

原创 2006年05月31日 20:41:00

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

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

backup_procedure.sql

  • 2015-05-07 14:48
  • 15KB
  • 下载

sql:MySql create FUNCTION,VIEW,PROCEDURE

use geovindu; #函数 DELIMITER $$ drop function if exists f_GetDepartmentName $$ CREATE function f_Ge...

SQL server 2008里面通过sys.dm_exec_procedure_stats得到存储过程的执行信息

----http://www.cnblogs.com/zhangxiaohuid/archive/2011/12/29/2306173.html 很多DBA都希望能够得到某个存储过程的执行信息,...

如何在SQL Server查询语句(Select)中检索存储过程(Store Procedure)的结果集?(

如何在SQL Server查询语句(Select)中检索存储过程(Store Procedure)的结果集?(2006-12-14 09:25:36) 与这个问题具有相同性质的其他描述还...

sql:Mysql create view,function,procedure

use test; create database Liber; use Liber; #顯示數据庫 20150210 Geovin Du 涂聚文 SHOW DATABASES; ...

sql: Oracle 11g create procedure

CREATE OR REPLACE PROCEDURE proc_Insert_BookKindList ( temTypeName nvarchar2, temParent int ) AS nc...

SQL Procedure 建立

--********************************************************************************************* -- ...

SQL Procedure示例程序03

ALTER PROCEDURE [dbo].[AssignAreaRightToUsers] AS BEGIN -- SET NOCOUNT ON added to prevent extra r...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)