Cursor coding horrors


Most coders have stereotypes of one kind or another. Given an opportunity, the developer will give into their stereotype. The coding guidelines is what keeps them from using it. I personally have a coding stereotype in T-SQL when defining a loop. Cursors are one of the most commonly used coding constructs. However, I tend to stick to my trusted friend a WHILE loop. I have worked on multiple issues where the T-SQL developer got the next FETCH wrong due to a oversight. Let me demonstrate that with an example. I have a T-SQL sample code which prints out all the user sessions connect to the SQL instance. 

declare @session_id int 

declare cur_sysprocesses cursor for 

select session_id from sys.dm_exec_requests 

where session_id > 50 

open cur_sysprocesses 

fetch next from cur_sysprocesses into @session_id 

while @@FETCH_STATUS <> -1 

begin 

print @session_id 

if (@session_id > 50) 

fetch next from cur_sysprocesses into @session_id 

end 

close cur_sysprocesses 

deallocate cur_sysprocesses

I have the next FETCH inside the while loop and the loop is governed by the value of @@FETCH_STATUS. Since, I have defined it as NOT EQUAL TO –1, the cursor will end when the next fetch is unsuccessful. This is a perfectly alright. Not all cursors in a production environment are that simple. Some of them have levels of nesting and WHILE loops which have deep levels of nesting along with conditional blocks. So, let me modify the example and show you: 

declare @session_id int 

declare cur_sysprocesses cursor for 

select session_id from sys.dm_exec_requests 

open cur_sysprocesses 

fetch next from cur_sysprocesses into @session_id 

while @@FETCH_STATUS <> -1 

begin 

print @session_id 

if (@session_id > 50) 

fetch next from cur_sysprocesses into @session_id 

end 

close cur_sysprocesses 

deallocate cur_sysprocesses

Now the problem with the above cursor is that most system processes have a session id below 50. So, now my cursor will loop through infinitely because the next fetch will never occur since the check in the conditional block will never evaluate to true as the first fetch from the cursor will always result in a system spid lesser than 50. This seems like a really amateur mistake which you think is not possible. But believe me, this happens! Since, I am showing this to you with a common system DMV, you think this is not possible. However, when you are using user defined tables and cursors, this is quite possible. Never define your next fetch from the cursor within the WHILE loop in a conditional block which is dependant on the data being fetched from the table. 

Always have the next FETCH defined in the first level of the while loop. Never ever put this in a conditional block. Always find an alternative way to find out a workaround or a different way to decide on whether to process the data fetched from the cursor. The next FETCH from the cursor should NOT be defined in an IF conditional block. 

It’s a human being that is coding the cursor. There is always a chance of some unforeseen circumstance (un-thought-of scenario) which makes your cursor go into a doomed state (read: infinite loop). 

You can never be dependant on your data to decide the fate of your code path. 

To summarize, the next FETCH while your looping through your cursor should be in the first level of your code unless and until you have no other choice and want to avoid an infinite loop scenario.

Advertisements

2 thoughts on “Cursor coding horrors

  1. Both the codes above are identical. It seems you forgot to modify the code.
    Let me know if I’m wrong.

  2. Please check the SELECT statement which is used to populate the cursor. The first one has a WHERE clause “where session_id > 50” where as the second one doesn’t. Both the cursor loops are the same but because the query used to populate the cursors are different, the first cursor has a finite number of iterations where as the second cursor is an infinite loop. You can run both the T-SQL scripts on a test machine and see for yourself!

    HTH

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s