Using a 2nd Cursor - 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)

Using a 2nd Cursor

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-25 : 14:11:18

I have to use two cursors in my stored procedure. I am using a stored procedure I found on Northwind as my guide. But it only has on cursor. How do I define a second cursor and bind it to a view?

/* Define Cursor */
DECLARE MyCursor cursor
Declare vwCursor cursor

/* Define 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

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

FOR SELECT sys, prin FROM dbo.vwSysPrins
open vwCursor;
FETCH next FROM vwcursor INTO @vwSys, @vwPrin

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-25 : 14:46:08

I doubt it

Why don't you tell use what you are trying to accomplish

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-25 : 15:19:56

Thanks Brett for responding so quickly. This is my first Stored Procedure and I have to batch some detail records according to a field (sys) contained in the detail record.

So I made a table (using Select Distinct) to create a view of all of the different ‘Sys’ values in the table. So I create a hdr whenever a new batch starts and follow it with its corresponding detail records. So I need a cursor for both the input detail table and the view that holds all the Sys values.

HDR
(i.e. 9 0001 3641 0080)
9 = batch HDR id
0001 – batch sequence
3641 = Sys
0080 = Prin

DETAIL
xxxxxxxxxxxxxx = Acct#
010 = trans type
23245 = amt
3641 = sys
0080 = prin

This is what it should look like
9 0001 3641 0080
0000000001 010 23245 3641 0080
0000000002 010 19265 3641 0080
0000000003 010 08845 3641 0080
9 0002 3641 0250
0000000501 010 00045 3641 0250
0000000900 010 19265 3641 0250
0000 001223 010 08845 3641 0250

Thanks again,
Trudye

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-25 : 16:47:52

I figured out how to create 2 cursors. Now I'm getting an error when I try to update my test for a change in value in my detail record.

i.e. While dbo.Daily_Trans.Sys = @vwSys
Where dbo.Daily_Trans.Sys is a field in my detail record. The other field is defined as:
DECLARE @vwSys varchar (4)

Error
The multi-part identifier dbo.Daily_Trans.Sys could not be bound

I have pasted in the table name so I know it is correct. I am in the right database and I pasted in the fieldname to insure it was correct.

Can anyone see what it is I am doing wrong.
Here is the rest of the code:
DECLARE @vwSys varchar (4)

My test for a change in value in my detail record.

i.e. While dbo.Daily_Trans.Sys = @vwSys
where dbo.Daily_Trans.Sys is a field in my detail record

Error
The multi-part identifier dbo.Daily_Trans.Sys could not be bound

I have pasted in the table name so I know it is correct. I am in the right database and I pasted in the fieldname to insure it was correct.

Can anyone see what it is I am doing wrong.
Here is the rest of the code.

USE [ApplyDev]
GO
/****** Object: StoredProcedure [dbo].[BatchNonMons] Script Date: 02/25/2008 12:25:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[BatchNonMons] 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.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 = dbo.Daily_Trans.Sys
BEGIN
SELECT Batch_Type, Batch_Number, Sys, Prin, Account_Number,
Transaction_Code, Clerk_Code, Memo_Text, SubTrans2,
Term_id, Op_Code
FROM dbo.Daily_Trans
WHERE Sys = @vwSys
/*Continue FETCH Command until EOF */
FETCH NEXT FROM MyCursor INTO
@Batch_Type, @Batch_Num, @Sys, @Prin, @Account_Num,
@Tran_Code, @Memo_Text, @SubTrans, @SubTrans_2,
@Terminal_Id, @Op_Code
SELECT Account_Number, Transaction_Code, SubTrans, Term_id, Op_Code
FROM dbo.Daily_Trans
WHERE dbo.Daily_Trans.Transaction_Code = '020'

SELECT Account_Number, Transaction_Code, @Clerk_Code, @Memo_Text
FROM dbo.Daily_Trans
WHERE dbo.Daily_Trans.Transaction_Code = '115'
SELECT Account_Number, Transaction_Code, SubTrans, SubTrans2, Term_id, Op_Code
FROM dbo.Daily_Trans
WHERE dbo.Daily_Trans.Transaction_Code = '177'
END
FETCH NEXT FROM vwCursor INTO @Sys, @Prin
SELECT Batch_Type, Batch_Number, Sys, Prin, Term_id, Op_Code
FROM dbo.Daily_Trans
END

CLOSE MyCursor
DEALLOCATE MyCursor
CLOSE vwCursor
DEALLOCATE vwCursor

Thank you,
Trudye

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-26 : 08:55:27

Thanks to everyone who read/ answered this question. I figured out why I was getting that msg. It seems the value I was trying to assign must come from the FETCH command on the second cursor.

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-26 : 12:58:22

I don't get it

What are you trying to do?

How many result sets are you getting

I really don't think you need a cursor, but I just went blind

Anyone?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 13:02:13

I doubt whether the OP is in an attempt to create the result set for some reporting needs.

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-26 : 13:17:09

quote:
Originally posted by visakh16

I doubt whether the OP is in an attempt to create the result set for some reporting needs.

I don't know what they're attempting

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-26 : 13:54:21

Hi Gusy these are all valid questions. I am using two cursors because I'm using one file to tell me when a group has ended and another file to tell me what the next group should be so I can write a hdr for the new group.
I have streamlined my code so the following may make more sense. My problem now is I can't get it to stop processing, it is in a infinite loop and I can't figure out why. Any input would be greatly appreciated.

USE [Apples]
GO
/****** Object: StoredProcedure [dbo].[BatchNonMons2] Script Date: 02/26/2008 12:51:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--DEBUG Stuff
-- PRINT
debug
ALTER PROCEDURE [dbo].[BatchNonMons2] AS

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

/* 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) --22
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. 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 --37
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
print @vwSys
print @Sys
BEGIN
INSERT INTO tblNonMon_Output --45
SELECT Account_Number, Transaction_Code, SubTrans, Term_id, Op_Code
FROM dbo.Daily_Trans
WHERE dbo.Daily_Trans.Transaction_Code = '020'

INSERT INTO tblNonMon_Output --45
SELECT Account_Number, Transaction_Code, @Clerk_Code, @Memo_Text
FROM dbo. Daily_Trans
WHERE dbo.Daily_Trans.Transaction_Code = '115'
INSERT INTO tblNonMon_Output --45
SELECT Account_Number, Transaction_Code, SubTrans, SubTrans2, Term_id, Op_Code
FROM dbo.Daily_Trans --55
WHERE dbo.Daily_Trans.Transaction_Code = '177'
/*Continue FETCH Command until EOF */
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
END
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
--65
END

CLOSE MyCursor
DEALLOCATE MyCursor
CLOSE vwCursor
DEALLOCATE vwCursor /* --71 */
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 14:28:03

Cant you try this using a WHILE loop? Post your full requirement with tables structure & sample data for more help.

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-26 : 15:11:37

OK, read my hint link in my sig and post what it asks for

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-26 : 15:52:18

For starters you PRINT statements are not even inside your loop, they are before the BEGIN block.

The real problem is that the inner while loop (WHILE @vwSys = @Sys) will alwasy be true. I *think* you need to change it to soemthing like: WHILE @vwSys = @Sys AND @@FETCH_STATUS = 0

That way you check the fetch status of vwCursor cursor. Currently you are only checking the status of MyCursor.

Try adjusting that and see if that helps.

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-27 : 08:55:25

I have a table of detail records that must be batched according to three fields (transaction_code, sys, prin). In other words every time the transaction_code, sys or prin changes in the detail record I must generate a new hdr for that group of records.

HDR Record
(i.e. 9 0001 3641 0080)
9 = batch HDR id
0001 – batch sequence
3641 = Sys
0080 = Prin

Dtl Record(s)
(i.e. 0000000001 010 23245 3641 0080)
0000000001 = Acct#
020 = transaction_Code
23245 = amt
3641 = sys
0080 = prin

0000000001 = Acct#
115 = transaction_Code
AAA = Clerk_Code
AAA Account Memo Information = Memo_Text

0000000001 = Acct#
177 = transaction_Code
xx = Sub_Trans

This is what the output should look like:
9 0001 3641 0080
0000000001 020 23245 3641 0080
0000000002 020 19265 3641 0080
0000000003 020 08845 3641 0080
9 0002 3641 0080
0000000001 115 AAA Account Memo Information
0000000002 115 AAA Account Memo Information
0000000003 115 AAA Account Memo Information
9 0003 3641 0080
0000000001 177xx
0000000002 177xx
0000000003 177xx

The above hdrs were generated based on a change in Transaction_Code (i.e. 020, 115, 177)

Here is an input sample
Acct_#
Batch_type
Batch_num
Sys
Prin
Transaction_code
Sub_trans
Clerk_Code
Memo_Text
Sub_trans
1 2 3 4 5 6 7 8 9 10
0000000306 NULL NULL 2012 0110 020 0 NULL NULL NULL
0000000306 NULL NULL 2012 0110 115 NULL AAA PPP NULL
0000000306 NULL NULL 2012 0110 177 0A NULL NULL 7
0000000735 NULL NULL 2012 0130 020 0 NULL NULL NULL
0000000735 NULL NULL 2012 0130 115 NULL AAA PPP NULL
0000000735 NULL NULL 2012 0130 177 0A NULL NULL 9
0000000091 NULL NULL 2012 0130 020 0 NULL NULL NULL
0000000091 NULL NULL 2012 0130 115 NULL AAA PPP NULL
0000000091 NULL NULL 2012 0130 177 0A NULL NULL 9
0000000005 NULL NULL 3641 0080 020 0 NULL NULL NULL
0000000005 NULL NULL 3641 0080 115 NULL AAA PPP NULL
0000000005 NULL NULL 3641 0080 177 0A NULL NULL 8
0000000089 NULL NULL 3641 0250 020 0 NULL NULL NULL
0000000089 NULL NULL 3641 0250 115 NULL AAA PPP NULL
0000000089 NULL NULL 3641 0250 177 0A NULL NULL 4
0000000075 NULL NULL 3641 1040 020 3 NULL NULL NULL
0000000075 NULL NULL 3641 1040 115 NULL AAA PPP NULL
0000000908 NULL NULL 3641 1800 020 3 NULL NULL NULL
0000000908 NULL NULL 3641 1800 115 NULL AAA PPP NULL

The data is not sorted like this I presented it like this to make a point (they are sorted in Transaction_code, Sys, Prin order). Which is each account number can have multiple transaction codes. There are other Sys and Prin numbers not shown here but the stored procedure should detect when there is a change in transaction_code, sys or prin. I was just trying to get the logic right for the Sys. I figured if I could get the break logic right the rest would be easy.

Let me know if I you need more information. I would like to do this with a cursor but if someone has an easy to understand way of doing it without a cursor let me know. It has to be easy to understand because I will have to change, modify and maintain the code.

Note; I left out two cols that are not important to the programming (Term_id, Op-Code) so the sample input would fit on one line.

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-27 : 09:35:54

quote:

Posted - 02/27/2008 : 08:55:25 Show Profile Email Poster Reply with Quote

I have a table of detail records that must be batched according to three fields (transaction_code, sys, prin). In other words every time the transaction_code, sys or prin changes in the detail record I must generate a new hdr for that group of records.

HDR Record
(i.e. 9 0001 3641 0080)
9 = batch HDR id
0001 – batch sequence
3641 = Sys
0080 = Prin

Dtl Record(s)
(i.e. 0000000001 010 23245 3641 0080)
0000000001 = Acct#
020 = transaction_Code
23245 = amt
3641 = sys
0080 = prin

0000000001 = Acct#
115 = transaction_Code
AAA = Clerk_Code
AAA Account Memo Information = Memo_Text

0000000001 = Acct#
177 = transaction_Code
xx = Sub_Trans

This is what the output should look like:
9 0001 3641 0080
0000000001 020 23245 3641 0080
0000000002 020 19265 3641 0080
0000000003 020 08845 3641 0080
9 0002 3641 0080
0000000001 115 AAA Account Memo Information
0000000002 115 AAA Account Memo Information
0000000003 115 AAA Account Memo Information
9 0003 3641 0080
0000000001 177xx
0000000002 177xx
0000000003 177xx

I still don't get it

How do the headers relate to the details?

Is the header info on the detail rows?

And what is this for?

A feed to another system?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-27 : 10:58:44

You are right the header information (transaction_Code, sys and Prin) have been placed in each detail record. They are determined and placed in the detail record when it is created. I then take the entire table of detail records and create a view of just the Sys and Prin field. So when the transaction, Sys or Prin changes the next record in the view should be read and the details that fall under that transaction, sys and prin should be output.

I found out what was wrong with my first FETCH and I corrected it. But for some reason the second FETCH that reads the view is not being executed at all. The first while is going into an infinite loop.

I hope that helped clear it up a little, if not let me know.

Thanks much

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-27 : 11:57:35

I figured out why it's looping. @@FETCH_Status is global and reflects the status of the last FETCH command.

Help suggested I query the sys.dm_exec_Cursor dynamic management func. Not real sure how to do that.

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-28 : 07:09:23

OK Guys, I am very, very close the solving this. I have figured out everything except how to loop. Every time I invoke a WHILE clause I go into an infinite loop. Without the WHILE clause I get 3 batches but it looks like the data is correct and in the right order. If I could figure out what my WHILE statement should check for I think I'll be very close to done. The system objects (ie. Invalid column name 'Sys') when I try to compare @Sys to the field value Sys . How do I set/update the field value for comparison to the Fetch/Array value? Any help would be appreciated.

Thanks,

Here is my new code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Batch]
AS
BEGIN
/* Create a new hdr each time the Sys/Prin changes in the Dtl rec */
TRUNCATE TABLE tblOutput

/* Define every field you want to output */ --21
DECLARE @Sys varchar (4)
DECLARE @vwSys varchar (4), @vwPrin varchar (4)
/* Define Cursor */
/*Works like an ARRAY*//*holds current record */
Declare vwCursor CURSOR FOR
SELECT Sys, Prin FROM dbo.vwNon
ORder by Sys, Prin
open vwCursor
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print '1st FETCH '
Print @vwSys
Print @vwPrin

WHILE @vwSys = @Sys
BEGIN
Print @vwSys
Print @vwPrin
INSERT INTO tblNonMon_Output (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP(1) Batch_Type, Batch_Number, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblNonMon_Output (Account_Num, Tran_Code, SubTrans, Terminal_Id, Op_Code) --45
SELECT Account_Number, Transaction_Code, SubTrans, Term_id, Op_Code
FROM dbo.tblTrans
WHERE dbo.tblCode = '020' AND Sys = @vwSys AND Prin = @vwPrin

INSERT INTO tblNonMon_Output (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) Batch_Type, Batch_Number, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblNonMon_Output (Account_Num, Tran_Code, Clerk_Code, Memo_Text) --45
SELECT Account_Number, Transaction_Code, Clerk_Code, Memo_Text
FROM dbo.tblTrans
WHERE dbo.tblCode = '115' AND Sys = @vwSys AND Prin = @vwPrin

INSERT INTO tblNonMon_Output (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) Batch_Type, Batch_Number, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblNonMon_Output (Account_Num, Tran_Code, SubTrans, SubTrans_2, Terminal_Id, Op_Code) --45
SELECT Account_Number, Transaction_Code, SubTrans, SubTrans2, Term_Id, Op_Code
FROM dbo.tblTrans --55
WHERE dbo.tblCode = '177' AND Sys = @vwSys AND Prin = @vwPrin

/*Continue FETCH Command until EOF */
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print ' 2nd FETCH '
Print @vwSys
Print @vwPrin
END --65

--print @vwSys
CLOSE vwCursor
DEALLOCATE vwCursor
End

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-28 : 12:49:02

You might want to re-read my post. You propogated the same issue from your cursor to your loop.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources