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 2005 Forums

Transact-SQL (2005)

Cursor Problem

Author

Topic

Vahaj
Starting Member

2 Posts

Posted - 2008-10-03 : 07:59:31

When Im executing the below cusor then it is getting hang and when I stop it then it shows the result but it is getting and I have to manually stop the Execute query.

Table

Create table authors
(au_id int)

insert into authors
values(1)

insert into authors
values(2)

insert into authors
values(3)

insert into authors
values(4)

and here is the cursor

DECLARE @AuthorID char(11)
DECLARE @a int
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

DECLARE @i float
select @i = count(*) from authors

set @i = (@i/100)*50

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0

Begin

while(@i<>0)

begin

PRINT @AuthorID
set @i = @i - 1

FETCH NEXT FROM c1
INTO @AuthorID

end
end

CLOSE c1
DEALLOCATE c1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 08:24:21

what's the purpose of this cusrsor? you're doing nothing but just printing AuthorIDs. ARe you learning cursor?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-03 : 08:28:08

If you are learning

FETCH NEXT FROM c1
INTO @AuthorID

end
end

should be

end

FETCH NEXT FROM c1
INTO @AuthorID

end

Madhivanan

Failing to plan is Planning to fail

Vahaj
Starting Member

2 Posts

Posted - 2008-10-03 : 09:32:58

Yes Im learning cursor this cursor is actually doing that it set the values in the @i variable and the loop runs until the value of @i reach to 0 but b/c of I put the fetch next statements in while loop it is getting hanged but it is showing the results after stopping it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-03 : 09:46:39

quote:
Originally posted by Vahaj

Yes Im learning cursor this cursor is actually doing that it set the values in the @i variable and the loop runs until the value of @i reach to 0 but b/c of I put the fetch next statements in while loop it is getting hanged but it is showing the results after stopping it.

Did you try my solution?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 09:50:34

quote:
Originally posted by madhivanan

quote:
Originally posted by Vahaj

Yes Im learning cursor this cursor is actually doing that it set the values in the @i variable and the loop runs until the value of @i reach to 0 but b/c of I put the fetch next statements in while loop it is getting hanged but it is showing the results after stopping it.

Did you try my solution?

Madhivanan

Failing to plan is Planning to fail

Personally i try to avoid using cursors as long as possible and use them only when i've no ways of dealing with problem in set based way, as a last resort.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-03 : 10:51:51

moved from script library

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-04 : 02:23:25

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by Vahaj

Yes Im learning cursor this cursor is actually doing that it set the values in the @i variable and the loop runs until the value of @i reach to 0 but b/c of I put the fetch next statements in while loop it is getting hanged but it is showing the results after stopping it.

Did you try my solution?

Madhivanan

Failing to plan is Planning to fail

Personally i try to avoid using cursors as long as possible and use them only when i've no ways of dealing with problem in set based way, as a last resort.

I agree with you. But OP said it is for learning purpose

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources