Cursor problems 1

[awhitney]

I'm new a creating stored procedures and using cursors. For some reason, my cursor does not work. Here is the problem area.

...

DECLARE cPersonnel CURSOR FAST_FORWARD
FOR SELECT personnel_key, login_name, failed_attempts, office_key
FROM Personnel WHERE (disabled = 'N')

set @msg = 'Cursor created'
print @msg
open cPersonnel
set @msg = 'Cursor opened'
print @msg
fetch next from cPersonnel into @p_personnel_key, @p_login_name, @p_level_key, @p_office_key
if (@@fetch_status <> 0)
begin
close Personnel_cursor
set @msg = 'Problem in the first fetch'
print @msg
return
end
While @@fetch_status = 0
set @msg = 'Populating user: ' + @p_login_name
print @msg
begin /* Get the users' Field office info associated with field/area office */
set @f_field_office_key = null
set @g_lmgroup_key = null
set @g_sec_level = null
...

The script stops before the first FETCH (the second print). HELP! I can see why they call this a cursor! >:-<

 

[JohnDTampaBay]

Do you really need a cursor? It's hard to tell what you're trying to do from the truncated code. Cursors are notorious resource hogs. Avoid cursors and looping if at all possible. A wise guru once told me, "Set-based is the true path, grasshopper." Having said that...

What do you mean "the script stops?" Is an error generated? If so, what is it? How many rows are returned from the cursor's SELECT?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[nigelrivett]

hmm
While @@fetch_status = 0
set @msg = 'Populating user: ' + @p_login_name
print @msg
begin /* Get the users' Field office info associated with field/area office */

that will execute
While @@fetch_status = 0
set @msg = 'Populating user: ' + @p_login_name

forever as it's before the begin.

see

http://www.nigelrivett.net/Cursors.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[awhitney]

Thanks for responding John,

You asked: "What do you mean "the script stops?" Is an error generated? If so, what is it? How many rows are returned from the cursor's SELECT?"

No error...all I see is:
Cursor created
Cursor opened

Then it just sits there with no rows returned.

I think I need a cursor because each record in the Personnel table has to get the correct office and security level and check or make entries into another table.

 

[nigelrivett]

Did you read my post - it tells you why that is happening and what to do about it?
try
set @msg = 'Cursor created'
print @msg
open cPersonnel
set @msg = 'Cursor opened'
print @msg
fetch next from cPersonnel into @p_personnel_key, @p_login_name, @p_level_key, @p_office_key
if (@@fetch_status <> 0)
begin
close Personnel_cursor
set @msg = 'Problem in the first fetch'
print @msg
return
end
While @@fetch_status = 0
begin
set @msg = 'Populating user: ' + @p_login_name
print @msg
set @f_field_office_key = null
set @g_lmgroup_key = null
set @g_sec_level = null


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.