Two inside loops - 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)

Two inside loops

Author

Topic

Jouni79
Starting Member

9 Posts

Posted - 2009-05-06 : 07:08:42

I am try make two inside loop with cursor. Tell me what i am doing wronng?
here loop part of my sql
--Delacration of Cursors
DECLARE Order_Cursor CURSOR FOR SELECT Reference,OrderHeaderID from OrderHeader WHERE NOT StateID=7 AND NOT StateID=8 AND NOT StateID=6
DECLARE Bashware_Cursor CURSOR FOR SELECT order_num,doc_id FROM BWEPP.dbo.docs where comp_no=30 AND status_index=3
OPEN Order_Cursor
FETCH NEXT FROM Order_Cursor INTO @Reference,@orderHeaderID
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF(Cursor_Status('local','Order_Cursor')=-1)
OPEN Bashware_Cursor
FETCH NEXT FROM Order_Cursor INTO @Reference,@orderHeaderID
print '1';
IF(Cursor_Status('local','Bashware_Cursor')=-1)
OPEN Bashware_Cursor
FETCH NEXT FROM Bashware_Cursor INTO @order_num,@doc_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM Bashware_Cursor INTO @order_num,@doc_id
print '2';
END

END

Output is like this at the moment:
1
2
2
2
and then loop ends
Both databases have 3 datarows, and output should be somethink like this:

1
2
2
2

1
2
2
2

1
2
2
2

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-06 : 07:30:02

Not to be glib here but where you are going wrong is probably to use the cursors in the first place.

What are you trying to achieve here (end result)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-06 : 07:36:41

Formatted Code:


--Delacration of Cursors
DECLARE Order_Cursor CURSOR FOR SELECT
	Reference
	, OrderHeaderID
from
	OrderHeader
WHERE
	NOT StateID = 7
	AND NOT StateID = 8
	AND NOT StateID = 6 

DECLARE Bashware_Cursor CURSOR FOR SELECT
	order_num
	, doc_id
FROM
	BWEPP.dbo.docs
where
	comp_no = 30
	AND status_index = 3	

OPEN Order_Cursor

	FETCH NEXT FROM Order_Cursor INTO @Reference,@orderHeaderID

	WHILE (@@FETCH_STATUS = 0) BEGIN

		IF(Cursor_Status('local','Order_Cursor')=-1) OPEN Bashware_Cursor

		FETCH NEXT FROM Order_Cursor INTO @Reference,@orderHeaderID

		print '1';

		IF(Cursor_Status('local','Bashware_Cursor')=-1) OPEN Bashware_Cursor

		FETCH NEXT FROM Bashware_Cursor INTO @order_num,@doc_id

		WHILE (@@FETCH_STATUS = 0) BEGIN
			FETCH NEXT FROM Bashware_Cursor INTO @order_num,@doc_id
			print '2';
		END
	END

Bleurgh.

Are you sure about your data.

Could you change the cursor to temp table versions and provide some data for testing?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Jouni79
Starting Member

9 Posts

Posted - 2009-05-06 : 08:07:35

I know there is no point to print 1 or 2.I am really novice with sql.I am just testing and practising.
Now I get error message:Cursor is not open, when I used your code.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-06 : 08:11:09

hmmm.

I didn't change anything (intentionally) from your code -- I just added the formatting for others. -- Maybe I altered something unintentionally.

Which cursor is it complaining about?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Jouni79
Starting Member

9 Posts

Posted - 2009-05-06 : 08:41:58

It just say Cursor is not OPEN.I know that where i have to update data between two tables. What is best way to do this. I Explain little bit more with exsample.

I have two databases(base1 and base 2), and both have Persons table.Time to time I have to check that all the information are same in these two databases.I mean Persons have same address and phone number and so on.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-06 : 09:25:10

you definitely don't need to use cursors for that!

Do you need both tables to end up with all the information from both tables (so if someone inserts into table A you need it to go to table B also and vica vers)

Or is one table the master and only info entered into that table should be replicated to the other?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources