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

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,Curr ID,ExtCmnt,FiscPer,FiscYear,GLAcctKey,JrnlKey,Jrnl No,PostAmt,PostAmtHC,PostCmnt,PostDate,PostQty,Sou rceModuleNo,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

tibornevermind, just saw my problem. of course it will loop if youre using the same table for insert and cursor, lol.|||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.|||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_profile.asp?mode=display&id=1578