Sql Storprocedure 的写法

/*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

GO