in ms sql, what exactly is a cursor?

[TechBoyJK]

im googling and reading stuff, but its still not quite clear to me. It sounds to me that a cursor is a record set, or a single record returned thats independent of the table that can be scrolled. anybody got a good cut and dry explanation?

 

[MrChad]

It's a pointer to a row in a recordset. It's commonly used in stored procedures to loop through each row in a recordset.

 

[TechBoyJK]

so.... its not a query... but the pointer is referencing a row from a query? rather than returning the entire record set of the query, you create a cursor on a query that returns a row one at a time? then you loop through that cursor? is the point of this to enhance resource usage, aka not sending the entire recordset from the query to a user all at once? i guess im having trouble grasping why i would use this. thanks~!

 

[MrChad]

Originally posted by: TechBoyJK
so.... its not a query... but the pointer is referencing a row from a query? rather than returning the entire record set of the query, you create a cursor on a query that returns a row one at a time? then you loop through that cursor? is the point of this to enhance resource usage, aka not sending the entire recordset from the query to a user all at once? i guess im having trouble grasping why i would use this. thanks~!

Say you wanted to return a result set as a comma-delimited list. You can use cursors to do this:

DECLARE @v_mylist VARCHAR(100), @v_myid int

SET @v_mylist = ''

DECLARE c_mycursor CURSOR
FOR SELECT myid FROM TABLE

OPEN c_mycursor
FETCH NEXT FROM c_mycursor INTO @v_myid

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @v_mylist = @v_mylist + CAST(@v_myid AS VARCHAR(5)) + ', '
FETCH NEXT FROM c_mycursor INTO @v_myid

END

CLOSE c_mycursor
DEALLOCATE c_mycursor

 

[MrChad]

Basically they're useful / convenient / necessary in certain situations.

 

[mAdMaLuDaWg]

A general rule of thumb is to avoid cursors as much as possible. If you ever need to use a cursor, you need to look into your SQL and see if it could be written without the use of cursors and/or possibly do some of the processing on the client-side. The overhead that cursors present you with aren't worth it at times. The problem is that once you get a hang of cursors you are likely going to use it for problems that could have been easily solved without them... so again, avoid their use as much as possible.