Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursors


Cursors

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

Group: General Forum Members
Points: 10091 Visits: 1
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: 1252 Visits: 63
What does sp_decribe_cursor do? Has anyone seen such function or was it a typo Sick?
Mike C
Mike C
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: 1349 Visits: 1168
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 (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

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

Group: General Forum Members
Points: 8321 Visits: 11554
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2871 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 (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 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
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 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
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 13305
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
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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