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.
Like this:
Like Loading...
TroubleshootingSQL 