SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursors


Cursors

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12715 Visits: 22
Comments posted to this topic are about the item Cursors
PrabodhM
PrabodhM
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1262 Visits: 63
What does sp_decribe_cursor do? Has anyone seen such function or was it a typo Sick?
Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6611 Visits: 1172
prabodh_castle (6/17/2008)
What does sp_decribe_cursor do? Has anyone seen such function or was it a typo Sick?


No that's a correct system SP. I'm trying to figure out how to get a count of qualifying rows with sp_describe_cursor_columns. Anyone have any examples of this? Here's an example from BOL that doesn't do this:

USE AdventureWorks;
GO
-- Declare and open a global cursor.
DECLARE abc CURSOR KEYSET FOR
SELECT LastName
FROM Person.Contact
GO
OPEN abc

-- Declare a cursor variable to hold the cursor output variable
-- from sp_describe_cursor_columns.
DECLARE @Report CURSOR

-- Execute sp_describe_cursor_columns into the cursor variable.
EXEC master.dbo.sp_describe_cursor_columns
@cursor_return = @Report OUTPUT,
@cursor_source = N'global', @cursor_identity = N'abc'

-- Fetch all the rows from the sp_describe_cursor_columns output cursor.
FETCH NEXT from @Report
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT from @Report
END

-- Close and deallocate the cursor from sp_describe_cursor_columns.
CLOSE @Report
DEALLOCATE @Report
GO
-- Close and deallocate the original cursor.
CLOSE abc
DEALLOCATE abc
GO

UPDATE: Oops, I missed the missing "s". Good catch, and you're right, that must be a typo.
emmanuelle.doumit
emmanuelle.doumit
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 54
It should be "sp_describe_cursor" not "sp_decribe_cursor", you are right it is a typo.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19039 Visits: 12426
As others already mentioned, sp_decribe_cursor is a typo. A so obvious typo that I didn't even catch it until I saw it being mentioned in the comments here. I can't imagine anyone getting the answer wrong because of that. Smile

Far more serious is the concern raised by Mike C. A concern I wanted to raise myself. The answer says that I can get the number of rows in the result of a cursor by couting rows returned by sp_describe_cursor_columns. However, this stored procedure returns one row per column in the result set of the cursor, not one per row. So I fail to see how counting the rows returned by this system stored procedure will learn me anything but the number of columns, rather than rows, in the cursor.

Of course, best practice would be to never use cursors at all - except for the 0.001% of all problems where a cursor does outperform a setbased solution. Wink


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Matija Lah
Matija Lah
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3511 Visits: 380
I agree with Hugo. Unless someone (preferably the author of the QOD) can show how rows can be counted using sp_describe_cursor_columns, the correct answer should be sp_cursor_list, sp_describe_cursor and @@cursor_rows.

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
liam.stirling
liam.stirling
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 124
Glad I'm not the only one - I can get number of rows in the cursor with all except sp_describe_cursor_columns...
Peter Rijs
Peter Rijs
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1418 Visits: 602
Had to refer to BOL to check out those sp's, since I don't use cursors (a Best practice as Hugo pointed out).
However, as the other commenters above already pointed out, I see no way to get or construct the number of rows in the cursor from the info sp_describe_cursor_columns returns, as all this info is about the columns in the cursor (as one probably might expext from a sp with that name Smile).
So QotD-poster: please show us the code that uses sp_describe_cursor_columns to count the original cursor's rows.

Peter Rijs
BI Consultant, The Netherlands
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24587 Visits: 13362
I see that others found out there's no way to get the number of rows in a cursor with sp_describe_cursor_columns, but I'll be glad to admit I was wrong if someone showed me the way to achieve this.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Venky-231781
Venky-231781
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 40
like others I see the sp_describe_cursor_columns returns only column_name, ordinal_position, column_characteristics_flags, column_size, data_type_sql, column_precision, column_scale, order_position, order_direction, hidden_column, columnid, objectid, dbid, dbname columns. Can anybody got clue on how to get the rows count using sp_describe_cursor_columns?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search