Using cursor in OLTP databases (SQL server)

Question

Is it safe to use cursors in stored procs that are called from a website? There is the obvious performance hit, but what I am trying to raise here is the issue with the variable @@Fetch_status. The scope of @@Fetch_status used in stored proc, is a connection. Isn't it possible that two different users call the same stored proc, from the same connection, thru the UI? Wouldn't that cause unexpected results?

In other words, would the fact that @@Fetch_status is global not just to a scope but to the entire connection, cause any concerns?

NOTE: This post is not about whether using a cursor is a good idea. I'd appreciate responses relevant to @@Fetch_status. Please come here to discuss the pros and cons of cursors

Quincuncial

Post some code showing how you'd use them. Their need is very few and far between. Also, it makes no difference if it's called from a website or otherwise. – Belabor

You can - but that doesn't mean you should !! The best thing with cursors is: avoiding them all together! – Enuresis

@yuck:I am not worried about any particular stored proc. My question is very general in nature. The difference between calling it from the UI and a window with SQL server management studio is that, every time you open a new window in a management studio, as far as @@Fetch_status is concerned, it is treated as a new connection. But when you call from an asp.net web application, due to connection pooling, the same connection could be used more than once. Hence there exists a possibility of the same @@Fetch_status being available for multiple transactions. – Quincuncial

@Quincuncial The same connection doesn't actually get used more than once. That's a misconception of how connection pooling works. – Belabor

@marc_s: I am not arguing whether it should or should not be used. I just want to know if my argument regarding the @@fetch_status is true. – Quincuncial

@Yuck: Then what is the difference between global to a scope and global to a connection? – Quincuncial

The only time you could have multiple sessions on the same connection is if you are using MARS, otherwise a single connection has a single session, thus you can only run one request per connection at any given time. – Rockwell

Regarding global to the connection vs. global to the scope, think of a temporary table. If you open a new query window in Management Studio you get a new connection and a new session. If you declare a temporary table, that table is available to any query you run in that window until you close it and the connection is reset. A local variable on the other hand is global to the scope only, you have to declare it every time you run a query. – Rockwell

bluefooted, your response answers my concern. Could you post that as the answer? – Quincuncial

Answer 1

Unless you are using global cursors you should be safe

that said, why are you using cursors, almost everything can be accomplished set based in sql server and it will be many times faster in almost every situation

Recollection

In response to your statement, if you need to say fix some data issue in production. You just need to run the script just once. If it is much easier to accomplish this using a cursor, would you still spend time trying to avoid cursors? What many people don't seem to agree with me is that, there is a difference between using cursors for one time processes and using them for OLTP related processes. What do you think? – Quincuncial