Home » SQL & PL/SQL » SQL & PL/SQL » getting error when calling procedure (9.0.4.0)
getting error when calling procedure [message #320210] Wed, 14 May 2008 05:39 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I have created 2 stored procedures as follows
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[REAL_PUSH_UPDATE_REPORTS] AS
BEGIN TRANSACTION
	DECLARE @cursor_contact_id bigint;
	DECLARE cursorContactId Cursor FOR SELECT distinct(contact_id) FROM [dbo].credit_reports WHERE loan_id IS NULL;
	OPEN cursorContactId;
	Fetch NEXT FROM cursorContactId INTO @cursor_contact_id;
IF(@@FETCH_STATUS <> 0)
PRINT 'There are no LOAN contacts are there with loan ID null, May be you have already executed this procedure'
	WHILE(@@FETCH_STATUS =0)
	BEGIN 
PRINT @cursor_contact_id;
		EXECUTE REAL_UPDATE_REPORTS @cursor_contact_id;
		Fetch NEXT FROM cursorContactId INTO @cursor_contact_id
	END
	CLOSE cursorContactId;
	DEALLOCATE cursorContactId;
	IF (@@Error = 0)
		BEGIN
			COMMIT TRANSACTION;
		END
	ELSE
		BEGIN
			ROLLBACK TRANSACTION;
		END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[REAL_UPDATE_REPORTS] @initial_contact_id bigint AS
BEGIN TRANSACTION
	DECLARE @loan_count bigint;
	DECLARE cursorLoanID Cursor FOR (SELECT l.loan_id loanIDList FROM (([dbo].loans l LEFT OUTER JOIN [dbo].loan_requests lr ON lr.loan_id=l.loan_id AND lr.contact_id = l.primary_borrower_id) LEFT OUTER JOIN [dbo].loan_codes lc ON l.loan_code_id = lc.loan_code_id)  WHERE (l.primary_borrower_id=@initial_contact_id) AND l.active=1  UNION  SELECT l.loan_id  FROM   [dbo].loans l LEFT OUTER JOIN [dbo].loan_requests lr ON lr.loan_id=l.loan_id LEFT OUTER JOIN [dbo].contacts c ON c.contact_id =l.primary_borrower_id WHERE (l.loan_id IN (SELECT cb.loan_id FROM coborrowers cb where contact_id =@initial_contact_id and active = 1)) UNION SELECT l.loan_id  FROM   [dbo].loans l LEFT OUTER JOIN [dbo].loan_requests lr ON lr.loan_id=l.loan_id LEFT OUTER JOIN [dbo].contacts c ON c.contact_id =l.primary_borrower_id WHERE (l.loan_id IN (SELECT cs.loan_id FROM cosigners   cs where contact_id =@initial_contact_id and active = 1)) UNION SELECT g.loan_id  FROM   [dbo].groups g, [dbo].group_members gm, [dbo].loan_requests lr WHERE gm.group_id = g.group_id AND lr.loan_id = g.loan_id AND lr.contact_id = gm.secondary_borrower_id AND gm.secondary_borrower_id=@initial_contact_id and gm.active = 1)  ORDER BY loanIDList DESC;
	OPEN cursorLoanID;
	SET @loan_count = @@CURSOR_ROWS;
PRINT @loan_count;
	IF(@loan_count > 0)	BEGIN
		DECLARE @loans_loan_id bigint;
		Fetch NEXT FROM cursorLoanID INTO @loans_loan_id;
		DECLARE @my_count bigint;
		SET @my_count=1;
		WHILE(@@FETCH_STATUS =0)
		BEGIN 
			DECLARE @temp_contact_id bigint;
			DECLARE @temp_loan_id bigint;
			SET @temp_contact_id = @initial_contact_id;
			SET @temp_loan_id = @loans_loan_id;
			IF(@my_count=@loan_count)
				BEGIN
					UPDATE [dbo].credit_reports SET loan_id = @temp_loan_id WHERE loan_id IS NULL AND contact_id = @initial_contact_id  AND NOT EXISTS (SELECT * FROM  [dbo].credit_reports WHERE contact_id = @initial_contact_id  AND loan_id=@temp_loan_id);
				END
			ELSE
				BEGIN
					INSERT INTO [dbo].credit_reports(contact_id,credit_bureau_id, credit_score, thirty_days_late, sixty_days_late, ninety_days_late, currently_negative, amount_past_due, inquiries_six_mos, public_records, collections, total_accounts_balance, total_mthly_pymts, report_file, report_gu_id, data_entry_by, data_entry_date, loan_id)  SELECT contact_id,credit_bureau_id, credit_score, thirty_days_late, sixty_days_late, ninety_days_late, currently_negative, amount_past_due, inquiries_six_mos, public_records, collections, total_accounts_balance, total_mthly_pymts, report_file, report_gu_id, data_entry_by, data_entry_date,@temp_loan_id   FROM [dbo].credit_reports WHERE contact_id = @initial_contact_id AND loan_id IS NULL AND NOT EXISTS (SELECT * FROM [dbo].credit_reports WHERE contact_id=@initial_contact_id AND loan_id=@temp_loan_id);
				END		
				Fetch NEXT FROM cursorLoanID INTO @loans_loan_id;				
			SET @my_count = @my_count + 1;
		END

close cursorLoanID
deallocate cursorLoanID

		
		IF (@@Error = 0)
			BEGIN
				COMMIT TRANSACTION;
				PRINT 'Success for contactID :'+CONVERT(varchar(50),@initial_contact_id);
			END
		ELSE
			BEGIN
				ROLLBACK TRANSACTION;
				PRINT 'Failed for contactID :'+CONVERT(varchar(50),@initial_contact_id);
			END
	END
	ELSE
	BEGIN
		ROLLBACK;
		PRINT 'NO Loans For the contactID :'+CONVERT(varchar(50),@initial_contact_id);
	END


now the problem is
i have executed 2 procedures saperately thn its ok while im calling im getting

Msg 16915, Level 16, State 1, Procedure REAL_UPDATE_REPORTS, Line 5

A cursor with the name 'cursorLoanID' already exists.

Msg 16905, Level 16, State 1, Procedure REAL_UPDATE_REPORTS, Line 6

The cursor is already open.


Please let me know the reason...
Thank you.
Re: getting error when calling procedure [message #320212 is a reply to message #320210] Wed, 14 May 2008 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why have I to horizontally scroll 20 screens to find the Reply button?

Regards
Michel
Re: getting error when calling procedure [message #320213 is a reply to message #320210] Wed, 14 May 2008 05:45 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This code is Sql-Server T-SQL unless I'm badly mistaken,

And this site is www.orafaq.com

So we may not be the best source of help you could find.
Previous Topic: full outer join
Next Topic: deadlock
Goto Forum:
  


Current Time: Sun Dec 04 10:30:27 CST 2016

Total time taken to generate the page: 0.07560 seconds