How to check whether cusor is open or not?
-
hiii,
Befor opening a cursor, I want to check whether it is already open or not??
How shud i chk it ??
-
I think a better question is, why do you want to use a cursor at all?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
What can one use instead of a cursur, when needed?
-
Okay, good point. But I think the situations where you truely need a cursor a *very* rare.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
To expand a bit on cursors (in answer of a PM to me, Remi
):I think cursors are viewed by most folks as some kind of SQL of the last resort. Cursor operate on a row-by-row basis which is somehow against the nature of RDBMS which operate best and most effectively when dealing with sets. I know cursors do look familiar in concept to people new to RDBMS or people with a background in procedural programming languages.
Usually cursors are, sometimes by orders of magnitude, slower than set-based solutions. So one should always seek to find such a set-based solution before even considering a cursor.
So, if someone feels the need to use a cursor, I would like to invite him posting the code along with some explaining words here. There are many good people around here, that really like to slash such cursors.
Having that said, I also think that the use of cursors for some internal DBA maintenance stuff is quite okay.
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Try using "IF Cursor_Status('variable', '@my_cursor') = -1"
**ASCII stupid question, get a stupid ANSI !!!** -
Y r u getting me involved in this Frank??
Anyways, I used cursors in production only once in my whole life (ok 13 months as a programmer) and it was for a dba task that uses the sp_msforeachtable procedure (which is a cursor in disguise).
I'm one of those guys that avoid cursors like pest and I can't say it's a bad thing to think like that. However I found 1 situation where cursors may be somewhat faster than the set based solution : Let's say you want to get the nth row (not a range) from a set, you can use a cursor to open the select statement and then goto that line anc extract the data.
So I will reask in the name of Frank : Why do you need to use a cursor for?
-
There may also be another hidden issue here. Lets assume a cursor is the "right thing" to do in this case. In normal SQL, one should know whether a cursor is open or closed. The only time I recall not knowing was when I was opening and closing a cursor inside of a loop. Certain logic in the loop could cause the cursor to remain open. The moral of the story is that my algorithm was screwed up. I reworked the process so that the cursor was declared/opened in a proc, and closed/deallocated on the way out of the proc. At the risk of showing how old I am, we used to call this structured programming way back in the 1970's.
Incidentally, I agree with Frank and Remi - why do you need a cursor?
Wayne
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply