//how to use template table of variables
//usage: execute delete_prodorder ‘AB5002-X’

create procedure delete_prodorder 
@ProdOrder nvarchar(30) 
as 
begin 
    --create temp table 
    create table #MyTableVar (OrgSpanKey    nvarchar(30)); 
    delete #MyTableVar 
    delete OPER_SPAN_RSRC where PROD_ORDER = @ProdOrder 
    --insert data 
    insert into #MyTableVar select ORG_SPAN_KEY FROM ate.OPER_SPAN where PROD_ORDER = @ProdOrder 
    delete OPER_SPAN where PROD_ORDER = @ProdOrder 
    --retreive data 
    delete ORG_SPAN where ORG_SPAN_KEY in (select * from #MyTableVar) 
    delete DOC_QUEUE WHERE  DOC_ID in (select distinct DOC_ID FROM OPER_DOC where PROD_ORDER = @ProdOrder) 
    update SHOP_MASTER set DTS_LAST_SYNC = null 
    drop table #MyTableVar 
end 
go

//how to use cursor to loop variables
//usage: replace your select cursor and stored procedure

declare	@prodOrder varchar(30) 
begin 
     declare prodOrderLoop cursor 
     for select ProdOrderId from Prod_Order 
     open prodOrderLoop 
     fetch next from prodOrderLoop into @prodOrder        

     while   @@FETCH_STATUS = 0 
        begin 
             execute delete_prodorder (@prodOrder) 
             fetch next from prodOrderLoop into @prodOrder 
        end     

     close prodOrderLoop 
     deallocate prodOrderLoop 
end 
go

//triggers example

create trigger OPERATION_DATA_UPD
on WOPR_DATA
after update
as
declare 

	@ProdOrder 		nvarchar(30),
	@OperNo			nvarchar(30),
	@OldOperStatus 		integer,	
	@QtyComplete		nvarchar(50),
	@Workspace 		nvarchar(50),
	@OldWorkspace		nvarchar(50),
	
	@s_notification1 	nvarchar(4000),
	@s_notification2 	nvarchar(4000),
	@s_notification3 	nvarchar(4000),
	@s_notification4 	nvarchar(4000),
	
	@string1 		nvarchar(4000),
	@string2 		nvarchar(4000),
	@string3 		nvarchar(4000),
	@string4 		nvarchar(4000)

begin
 
  select 	@ProdOrder 	= rtrim(a.WOPR3_ORD_REF), 	
  		@OperNo 	= cast(a.WOPR3_NO as varchar),
  		@QtyScrap 	= cast(a.WOPRQTY_SCRAPPED - b.WOPRQTY_SCRAPPED as varchar),
  		@Workspace 	= a.WOPR3_WORK, 		
  		@OldWorkspace 	= b.WOPR3_WORK,


  from 		Inserted  a, Deleted b  
  where       	a.WOPR3_ORD_REF = b.WOPR3_ORD_REF
  and         	a.WOPR3_NO = b.WOPR3_NO
  

  if @Workspace != @OldWorkspace 
  begin
    set  	@s_notification2='"
             Change'+@ProdOrder +'"'
    set  	@string2= 'd:\nmetric\notification\run-notification 127.0.0.1 20000 '+@s_notification2
    exec 	master..xp_cmdshell @string2  
    
    set  	@s_notification3='"
             INQUEUEChange'+@ProdOrder +'"'
    set  	@string3= 'd:\nmetric\notification\run-notification 127.0.0.1 20000 '+@s_notification3
    exec 	master..xp_cmdshell @string3      
  end   
end
go

Ref:
cursor overview
stored procedure
triggers I
triggers II
How to Share Data Between Stored Procedures

Advertisements