Sql Storprocedure 的写法

最新推荐文章于 2020-04-09 20:34:02 发布

puserzhou

最新推荐文章于 2020-04-09 20:34:02 发布

阅读量790

点赞数

分类专栏： IT技术集锦文章标签： sql insert float null table date

本文链接：https://blog.csdn.net/puserzhou/article/details/604369

版权

IT技术集锦专栏收录该内容

19 篇文章 0 订阅

订阅专栏

/*Created By Bozhou in 2006/1/11 as Caculate Balance Qty*/
CREATE procedure sp_MKS_killPlan_qty
/*(
                 @thisdayfrom DateTime,
                 @thisdayto DateTime,
                 @thisday     int
) */
as

declare @planqty float,@rcvqty float,@balanceqty float,@rowcount int, @itemno varchar(20), @thisdayfrom DateTime,@thisdayto DateTime, @thisday int

create table #ITEM
(
ITEM_NO nvarchar(50)
)

DECLARE SSItem_Cursor CURSOR FOR select ITEM_NO from #ITEM
begin

    ----串时间的起始时间
        if getdate() >cast(convert(nvarchar,getdate(),111)+' 08:00' as datetime)
begin
  set @thisdayfrom=cast(convert(nvarchar,getdate(),111)+' 08:00' as datetime)
  set @thisdayfrom=cast(convert(nvarchar,getdate()+1,111)+' 08:00' as datetime)
  set @thisday=Convert(Int,Convert(nvarchar,getdate(),112))
end

        else
begin
  set @thisdayfrom=cast(convert(nvarchar,getdate()-1,111)+' 08:00' as datetime)
  set @thisdayfrom=cast(convert(nvarchar,getdate(),111)+' 08:00' as datetime)
  set @thisday=Convert(Int,Convert(nvarchar,getdate()-1,112))
end

    ----串时间的结束时间

--- BEGIN TRAN T1
insert into #ITEM(ITEM_NO) select ITEM_NO from C_MKS_ITEM_INUSE where status = 'Y'

      -- SELECT @count =count(*) from C_MKS_ITEM_INUSE a left join C_MKS_ITEM_GROUP b
      -- on a.group_id = b.group_id where a.status = 'Y'

       OPEN SSItem_Cursor

     -- fetch first from SSItem_Cursor into @itemno

         FETCH NEXT FROM SSItem_Cursor INTO @itemno
         WHILE (@@FETCH_STATUS = 0 )
begin

        -- print @itemno
         -- if( @itemno is not null)
        -- begin
               select @rcvqty=sum(RCV_QTY_H) from I_MKS_ERP_RCV where RCV_TIME>=@thisdayfrom and RCV_TIME<@thisdayto
               and ITEM_NO=@itemno

               select @planqty=sum(PLAN_QTY_H)   from I_MKS_ITEM_PLAN where ITEM_NO=@itemno
                 and PLAN_TIME>=@thisdayfrom and PLAN_TIME<@thisdayto

                   if @rcvqty is null set @rcvqty=0
                   if @planqty is null set @planqty=0

                 set @balanceqty=@planqty-@rcvqty

                   select @rowcount=count(*) from H_MKS_ITEM_BALANCE where BALANCE_DATE=@thisday and ITEM_NO=@itemno
                   if @rowcount>0
                   update H_MKS_ITEM_BALANCE set RCV_QTY=@rcvqty,PLAN_QTY=@planqty,BALANCE_QTY=@balanceqty
                   where ITEM_NO=@itemno and BALANCE_DATE=@thisday
                   else
                    insert into H_MKS_ITEM_BALANCE values(@itemno,@rcvqty,@planqty,@balanceqty,@thisday)

-- select @count = @count-1
-- end

       -- COMMIT TRAN T1
        FETCH NEXT FROM SSItem_Cursor INTO @itemno
end
        close SSItem_Cursor
        drop table #ITEM
         insert into C_MKS_SYS_LOG (JOB_TYPE,JOB_DESC, JOB_STATUS,CREATE_TIME)
         values('SP','计算Balance数量','Finished',getdate())
end