Odd Cursor Behavior (Don’t Try This At Home)

Disclaimer: I know that the below code snippet is evil, but I was just curious about why this would happen. I would recommend you don’t do this in a production system.

Over the past several days I’ve been working on a script that will modify all of the tables in a database, adding, renaming, and changing the type of several columns. The script will be used during the deployment of my Service Broker Replication project that I’ll be blogging more about soon. One of the things the script needs to do is find all of the dependencies (PKs, FKs, views, etc.)  that need to be dropped (and later recreated) to allow the modifications I mentioned above to succeed.

When I started writing the script, I figured I would only need to worry about one level of dependencies, but as it turns out there are multiple levels of dependencies that have dependencies and so on. This discovery led me to change my script into a couple of stored procedures, one of which will recursively call itself to traverse the dependency chain. As I was playing around with a couple different ways to do recursion in SQL Server, I noticed the odd behavior below (since the scenario itself is totally crazy to begin with, I’m not surprised that the behavior is odd too).

If you have a cursor that loops through a temp table and also adds records to the temp table within the cursor itself, it is unpredictable how many times the infinite loop will execute before the query completes. The fact that the query completes at all and doesn’t just continue running forever is weird as well.

Again, I reiterate, don’t do this in any system that you build!

set nocount on;

declare @Id bigint, @Message varchar(max);

create table #Test (Id bigint);
insert #Test (Id) values (0);

declare curTest cursor local fast_forward for select Id from #Test;
open curTest;
fetch next from curTest into @Id;

while @@FETCH_STATUS = 0
begin
insert #Test (Id) select @Id + 1;

set @Message = cast(@id as varchar(max));
raiserror(@Message, 0, 1);

fetch next from curTest into @Id;
end;

close curTest;
deallocate curTest;

drop table #Test;

I’m not really sure why I get a different ending number each time I run this. If anyone can shed some light on this, please post a comment. Otherwise, try it out in your lab and enjoy the weirdness.

}:-)

Leave a Reply