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  |
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.
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 18 01:41:40 CST 2025
|