Cursor Already Exisits Error

[jgurgen]

I have a stored procedure that i call when ever there is an insert on the database. The problem is is that in the stored procedure i have an insert statement that will then recall the stored procedure causing it run while its already running. How can i run the procedure on inserts with out having them overlap each other? is there a pause if running?

 

[SQLSister]

Np there is no pause command. To help you I think we need more information can you give us an example of the code you are having a problem with? Incidentallly I would under no circumstances use a cursor to do an insert as they are performance killers and inserts can be done against the whole set of records much faster.

Questions about posting. See faq183-874

 

[jgurgen]

sure here is the procedure
i changed it up a bit. instead of inserting into into the same table im inserting into a temp table where i will do the insert back into the regular table afterwards. the original insert is commented out above the new insert

CREATE PROCEDURE [dbo].[P_iSplit_Disb_1] 
( @LoanID varchar(10) )
AS


-- PAYMENT CURSOR VARIABLES
DECLARE @P_LoanID varchar(10),
	@P_HC smallint,
	@P_ID int, 
	@P_TD datetime,
	@P_TC smallint,
	@P_TA money,
	@P_TT bit, 
	@P_CD datetime,
	@P_DO int
	
DECLARE @D_LoanID varchar(10),
	@D_HC smallint,
	@D_ID int, 
	@D_TD datetime,
	@D_TC smallint,
	@D_TA money,
	@D_TT bit, 
	@D_CD datetime,
	@D_DO int,
	@Balance decimal,
	@Temp decimal,
	@message varchar(80)

	SET @Balance=0

DECLARE Payment_Cursor CURSOR
FOR
SELECT A.LoanID, A.HistoryCounter, A.ID, A.TranDate, A.TranCode, A.TranAmt, A.TranType, A.ClosingDate, A.DaysOpen
FROM iSplit_Disb_Includes A
WHERE A.LoanID=@LoanID AND (A.TranType=1 AND A.Used=0)

OPEN Payment_Cursor 	-- Opens Cursor for reading

FETCH NEXT FROM Payment_Cursor INTO @P_LoanID, @P_HC, @P_ID, @P_TD, @P_TC, @P_TA, @P_TT, @P_CD, @P_DO
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @Balance = @P_TA	-- SETS BALANCE TO VALUE OF TRANSACTION

	DECLARE Disb_Cursor CURSOR
	FOR
	SELECT A.LoanID, A.HistoryCounter, A.ID, A.TranDate, A.TranCode,  A.TranAmt, A.TranType, A.ClosingDate, A.DaysOpen
	FROM iSplit_Disb_Includes A
	WHERE A.LoanID=@LoanID AND (A.TranType=0 AND A.Used=0)
	
	Open Disb_Cursor
	/*------------------------------------------------------------------------------------------------------*/
	/*------------------------------FETCH DISBURSEMENTS----------------------------*/
	/*------------------------------------------------------------------------------------------------------*/	
		FETCH NEXT FROM Disb_Cursor INTO @D_LoanID, @D_HC, @D_ID, @D_TD, @D_TC, @D_TA, @D_TT, @D_CD, @D_DO	
		
		IF @@FETCH_STATUS<>0
		PRINT 'PAYMENT  -  ' + CONVERT(VARCHAR(10), @P_ID)
	
		WHILE @@FETCH_STATUS = 0
		BEGIN
		/* FOR EACH PAYMENT APPLY TO AS MANY DISBURSEMNTS AS POSSIBLE */	
		PRINT 'PAYMENT  -  ' + CONVERT(VARCHAR(10), @P_ID)
		PRINT 'DISBURSEMENT  -  ' + CONVERT(VARCHAR(10),  @D_ID)
		PRINT 'BEGIN DISBURSEMENT'

		IF(@Balance <> 0)
		BEGIN
		PRINT 'Balance <> 0   ' + CONVERT(VARCHAR(10), @Balance)
			SET @Temp = @Balance - @D_TA
			
			IF @Temp > 0 -- Payment paid off disbursement, money left over from payment
			BEGIN
				PRINT 'Temp > 0   '  + CONVERT(VARCHAR(10), @Balance)
				SET @Balance = @Temp
				-- Update Disbursement as used
				IF @P_TD < @D_TD
				UPDATE iSplit_Disb_Includes SET Used=1, ClosingDate=@D_TD WHERE ID=@D_ID
				ELSE
				UPDATE iSplit_Disb_Includes SET Used=1, ClosingDate=@P_TD WHERE ID=@D_ID
			END
			ELSE IF @Temp < 0 -- Payment was not enough, cut off disbursement and end payment
			BEGIN
				PRINT 'Temp < 0   '  + CONVERT(VARCHAR(10), @Balance)
				SET @Balance = @D_TA - @Balance
				-- Update Payment as used
				UPDATE iSplit_Disb_Includes SET Used=1 WHERE ID=@P_ID
				-- Update Disbursement as used
				IF @P_TD < @D_TD
				UPDATE iSplit_Disb_Includes SET Used=1, ClosingDate=@D_TD WHERE ID=@D_ID
				ELSE
				UPDATE iSplit_Disb_Includes SET Used=1, ClosingDate=@P_TD WHERE ID=@D_ID
				-- Insert New Disbursement for left over balance
				-- INSERT INTO iSplit_Disb_Transactions (LoanID, HistoryCounter, TranDate, TranCode, TranAmt, SysGen) VALUES (@D_LoanID, @D_HC, @D_TD, @D_TC, @Balance, 1)
				INSERT INTO iSplit_Disb_TranTemp (LoanID, HistoryCounter, Ins, TranDate, TranCode, TranAmt, SysGen) VALUES (@D_LoanID, @D_HC,  1, @D_TD, @D_TC, @Balance, 1)

				SET @Balance = 0
			END
			ELSE -- TEMP =  ZERO 
			BEGIN
				PRINT 'Temp = 0   '  + CONVERT(VARCHAR(10), @Balance)
				-- Update Payment as used
				UPDATE iSplit_Disb_Includes SET Used=1 WHERE ID=@P_ID
				-- Update Disbursement as used
				UPDATE iSplit_Disb_Includes SET Used=1, ClosingDate=@P_TD WHERE ID=@D_ID
			END
		END -- END IF(@Balance<>0)

		PRINT 'END OF DISBURSEMENT   ' + Convert(Varchar(10), @Balance) + '  :  ' + Convert(Varchar(10), @Temp)
		/* FETCH NEXT DISBURSEMENT */
		FETCH NEXT FROM Disb_Cursor INTO @D_LoanID, @D_HC, @D_ID, @D_TD, @D_TC, @D_TA, @D_TT, @D_CD, @D_DO	
		
		END -- END WHILE LOOP - DISBURSEMENTS
	
		/* CLOSE OUT CURSOR */
		CLOSE Disb_Cursor -- Closes Cursor
		DEALLOCATE Disb_Cursor -- Clears Memory
	/*------------------------------------------------------------------------------------------------------*/
	/*------------------------END FETCH DISBURSEMENTS--------------------------*/
	/*------------------------------------------------------------------------------------------------------*/	

	/*
	IF the balance doesnt equal the original payment amount that means that disbursements were paid back useing this payment.
	and if blanace is greater than zero shows that there is still some of the payment left to be paid back.  
	there for a closing of the original payment and an insert of a new payment must be made.  The new payment will have the left over balance  
	*/
	IF @Balance <> @P_TA AND @Balance >0
	BEGIN
		-- Update Payment as used
		UPDATE iSplit_Disb_Includes SET Used=1 WHERE ID=@P_ID
		-- INSERT INTO iSplit_Disb_Transactions (LoanID, HistoryCounter, TranDate, TranCode, TranAmt, SysGen) VALUES (@P_LoanID, @P_HC, @P_TD, @P_TC, @Balance, 1)
		INSERT INTO iSplit_Disb_TranTemp (LoanID, HistoryCounter, Ins, TranDate, TranCode, TranAmt, SysGen) VALUES (@P_LoanID, @P_HC,  1, @P_TD, @P_TC, @Balance, 1)
	END

	/* FETCH NEXT PAYMENT */
	FETCH NEXT FROM Payment_Cursor INTO @P_LoanID, @P_HC, @P_ID, @P_TD, @P_TC, @P_TA, @P_TT, @P_CD, @P_DO
END -- END WHILE LOOP - PAYMENTS

/* CLOSE OUT CURSOR */
CLOSE Payment_Cursor -- Closes Cursor
DEALLOCATE Payment_Cursor -- Clears Memory

PRINT 'Preparing calculation for DaysOpen...'
/* Execute Procedure for daysopen*/
EXEC P_iSplit_Disb_DateDiff @LoanID  --  Calculate days open
GO

 

[goitz]

I totally agree with SQLSister using a cursor to do this will kill your performance especially if your doing this every time on a insert. I would insert all your records into a temp table to do your work then insert into the main table as a final step.