cursor problem - 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 2000 Forums

Transact-SQL (2000)

cursor problem

Author

Topic

cheriansabs
Starting Member

2 Posts

Posted - 2006-08-08 : 08:57:00

hi people,
i have registered recently on this forum.
I got a basic doubt bout cursors
This is my cursor contained inside stored procedure

DECLARE GetSQL_Cursor CURSOR
FOR
SELECT 'T'+ Ltrim(str(idx)),value FROM dbo.fn_Split(@responseMsg1,@sDelim) ORDER BY idx
OPEN GetSQL_Cursor
WHILE @@Fetch_Status = 0
begin
FETCH NEXT FROM GetSQL_Cursor INTO @alias, @value
if convert(int,@value)>=20 and convert(int,@value)<30
begin
break
end
end
when i run this code using sql Query analyser it wks without any problems
but when i run this stored procedure using asp.net code it does not enter the while loop.
any clues why.
thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-08 : 12:44:50

Can you explain what you are trying to do?
There can be better method than what you are trying.
Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

cheriansabs
Starting Member

2 Posts

Posted - 2006-08-09 : 02:40:09

hi madhav,
I did find a way out of this problem.
Instead of using @@fetchstatus.
I found out the maximum no of records my function split was returning.
and then i ran a loop against a counter.
the whole problem is with @@fetchstatus.
It is a global varable for all connections.
so once my loop was over it set @@fetchstatus=-1
so when i tried running the loop thru asp.net it found out tht @@fetchstatus=-1 and then would enter the while loop.
I hope this helps out anybody with a similar prob
thank you guys

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-09 : 04:10:27

Why don't you do a fetch after opening it, before checking @@fetch_status?

The standard thing for cursors is:

declare
open
fetch
while @@fetch_status = 0
begin
do_stuff
fetch
end
close
deallocate

Just seems to me you've missed some bits...

Rob Farley
http://robfarley.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources