Problem Declaring Cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem Declaring Cursors

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-26 : 09:01:58

I am still having problems with my cursors. I am receiving the following errors when I execute my Stored Procedure:
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 27
A cursor with the name 'MyCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 31
The cursor is already open.
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 37
A cursor with the name 'vwCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 38
The cursor is already open.

Here is the code causing the problem:

CREATE PROCEDURE [dbo].[BatchNonMons2] AS

BEGIN
/* Create a new hdr each time the Sys/Prin changes in the Dtl rec */

/* Define every field you want to output */
DECLARE @Batch_Type int
DECLARE @Batch_Num int
DECLARE @Sys varchar (4)
DECLARE @Prin varchar (4)
DECLARE @Account_Num varchar(16)
DECLARE @Tran_Code varchar(3)
DECLARE @Clerk_Code varchar(3)
DECLARE @Memo_Text varchar(57)
DECLARE @SubTrans varchar(2)
DECLARE @SubTrans_2 varchar(1)
DECLARE @Terminal_Id varchar(3)
DECLARE @Op_Code varchar(2)
DECLARE @Last4 varchar(6)

DECLARE @vwSys varchar (4)
DECLARE @vwPrin varchar (4)

/* Define Cursor */
DECLARE MyCursor cursor
For SELECT Batch_Type, Batch_Number, Sys, Prin, Account_Number, Transaction_Code,
Clerk_Code, Memo_Text, SubTrans, SubTrans2, Term_id, Op_Code
FROM dbo.tblNonMon_Daily_Trans

Open MyCursor; /*Works like an ARRAY*//*holds current record */
FETCH NEXT FROM MyCursor INTO @Batch_Type, @Batch_Num, @Sys, @Prin, @Account_Num,
@Tran_Code, @Clerk_Code, @Memo_Text, @SubTrans, @SubTrans_2,
@Terminal_Id, @Op_Code

Declare vwCursor cursor
FOR SELECT Sys, Prin FROM dbo.vwNonMonSysPrins
open vwCursor;
FETCH next FROM vwcursor INTO @vwSys, @vwPrin

/* When @@Fetch_Status = 1 EOF has been reached */
WHILE @@Fetch_Status = 0
BEGIN
WHILE @vwSys = @Sys
BEGIN

Any input would be appreciated.
Thanx,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 09:04:09

After you are done with cursor you have to write below two statements:

close <cursor-name>
deallocate <cursor-name>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-26 : 09:56:25

Thank you so much for responding so quickly harsh. You are correct oh wise one. I had the code to cose and deallocate at the bottom of my stored procedure. Obviously it never executed, I'll have to find out why. But you got it right on the button.

Thank you again,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources