Results 1 to 4 of 4

Thread: Cursor

  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Cursor

    hey all,
    i am relatively new to cursors and have created a SP that uses a cursor to populate a table. here is the code

    Code:
    CREATE             PROCEDURE sppa_invvoid
    		(	
    		@invno	int
    		)
    
     AS
    
    DECLARE      @glTranKey int   
    DECLARE      @AcctRefKey int  
    DECLARE      @BatchKey int   
    DECLARE      @CreateDate datetime  
    DECLARE      @CreateType smallint   
    DECLARE      @CurrExchRate float   
    DECLARE      @CurrID varchar (3)
    DECLARE      @ExtCmnt varchar (255)
    DECLARE      @FiscPer smallint   
    DECLARE      @FiscYear varchar (5) 
    DECLARE      @GLAcctKey int   
    DECLARE      @JrnlKey int  
    DECLARE      @JrnlNo int  
    DECLARE      @PostAmt decimal(15, 3)   
    DECLARE      @PostAmtHC decimal(15, 3)   
    DECLARE      @PostCmnt varchar (50) 
    DECLARE      @PostDate datetime   
    DECLARE      @PostQty decimal(16, 8)   
    DECLARE      @SourceModuleNo smallint   
    DECLARE      @TranDate datetime  
    DECLARE      @TranKey int  
    DECLARE      @TranNo varchar (10) 
    DECLARE      @TranType int  
    DECLARE     @Companyid  varchar(3)
    DECLARE      @Batchtype  int
    DECLARE     @Userid  varchar (30)
    DECLARE      @Moduleno   int
    DECLARE      @NextBatchNo  int
    DECLARE      @_oRetVal int
    DECLARE       @iTableName	varchar(50)
    DECLARE       @iCommitFlag int
    DECLARE        @NextJrnlNo   int
    
    
    Set @CompanyID= 'EMA'
    Set @JrnlNo = 139
    Set @iCommitFlag = 1
    Set @JrnlKey = 193
    Set @iTableName='tgltransaction'
    Set @batchtype = 501
    Set @moduleNo = 5
    Set @Userid = 'Admin'
    
    
    
    EXECUTE spGetNextBatchNo @CompanyID, @BatchType, @UserId, @ModuleNo, @BatchKey OUTPUT, @NextBatchNo OUTPUT, @_oRetVal OUTPUT
    	
    Execute spglGiveNextJrnlNo @CompanyID, @JrnlKey, @iCommitFlag,  @JrnlNo, @NextJrnlNo OUTPUT
    	
    
    
    
    DECLARE cursor_tran CURSOR FOR 
    
    select  glTranKey, AcctRefKey,CreateDate,CreateType,CurrExchRate,CurrID,ExtCmnt,FiscPer,FiscYear,GLAcctKey,JrnlKey,JrnlNo,PostAmt,PostAmtHC,PostCmnt,PostDate,PostQty,SourceModuleNo,TranDate,TranKey,TranNo,TranType
    from  tgltransaction where tranno = @invno
    
    OPEN cursor_tran
    
    FETCH NEXT FROM cursor_tran INTO 
    
    @glTranKey,
    @AcctRefKey,
    @CreateDate,
    @CreateType,
    @CurrExchRate,
    @CurrID,
    @ExtCmnt,
    @FiscPer,
    @FiscYear,
    @GLAcctKey,
    @JrnlKey,
    @JrnlNo,
    @PostAmt,
    @PostAmtHC,
    @PostCmnt,
    @PostDate,
    @PostQty,
    @SourceModuleNo,
    @TranDate,
    @TranKey,
    @TranNo,
    @TranType
    
    
    
    
    
    WHILE (@@FETCH_STATUS <>  -1)
    
    	BEGIN		
    
    Execute spgetnextSurrogateKey @iTablename , @glTranKey OUTPUT
    /*Execute aaaa*/
    			
    
    
    	
    
    		INSERT INTO 		tgltransaction
    					(glTranKey,
    					AcctRefKey,
    					BatchKey,
    					CreateDate,
    					CreateType,
    					CurrExchRate,
    					CurrID,
    					ExtCmnt,
    					FiscPer,
    					FiscYear,
    					GLAcctKey,
    					JrnlKey,
    					JrnlNo,
    					PostAmt,
    					PostAmtHC,
    					PostCmnt,
    					PostDate,
    					PostQty,
    					SourceModuleNo,
    					TranDate,
    					TranKey,
    					TranNo,
    					TranType)
    					
    		Values
    					
    					(@glTrankey,
    					@AcctRefKey,
    					@BatchKey,
    					@CreateDate,
    					@CreateType,
    					@CurrExchRate,
    					@CurrID,
    					@ExtCmnt,
    					@FiscPer,
    					@FiscYear,
    					@GLAcctKey,
    					@JrnlKey,
    					@JrnlNo,
    					@PostAmt,
    					@PostAmtHC,
    					@PostCmnt,
    					@PostDate,
    					@PostQty,
    					@SourceModuleNo,
    					@TranDate,
    					@TranKey,
    					@TranNo,
    					@TranType)
    
    
    
    
    FETCH NEXT FROM cursor_tran INTO 
    
     @glTranKey,
    @AcctRefKey,
    @CreateDate,
    @CreateType,
    @CurrExchRate,
    @CurrID,
    @ExtCmnt,
    @FiscPer,
    @FiscYear,
    @GLAcctKey,
    @JrnlKey,
    @JrnlNo,
    @PostAmt,
    @PostAmtHC,
    @PostCmnt,
    @PostDate,
    @PostQty,
    @SourceModuleNo,
    @TranDate,
    @TranKey,
    @TranNo,
    @TranType
    
    
    
    END
    	CLOSE cursor_tran
    	DEALLOCATE cursor_tran
    GO
    the issue that i am having is after i do the insert, the cursor picks up on the inserted row and it ultimately becomes an infinate loop. what can i do to prevent it from picking up the newly inserted rows. thanks alot

    tibor

  2. #2
    Join Date
    Mar 2006
    Posts
    82
    nevermind, just saw my problem. of course it will loop if youre using the same table for insert and cursor, lol.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Glad to see you've figured out how to use cursors. Now do yourself a favor and forget about them, and use set-based processing instead.

    Your spglGiveNextJrnlNo procedure should be converted to a user-defined function, (or better yet, dumped altogether), and then you can write your code as a much shorter and much more efficient INSERT statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey, a wiseman once said:

    Cursors are useful if you don't know sql.
    DTS can be used in a similar way.
    Beer is not cold and it isn't fizzy.
    http://www.sqlteam.com/forums/pop_pr...isplay&id=1578
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •