Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Cursors Expand / Collapse
Author
Message
Posted Tuesday, June 17, 2008 8:45 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item Cursors
Post #518714
Posted Tuesday, June 17, 2008 10:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 29, 2013 6:28 AM
Points: 1,252, Visits: 63
What does sp_decribe_cursor do? Has anyone seen such function or was it a typo ?
Post #518731
Posted Tuesday, June 17, 2008 10:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
prabodh_castle (6/17/2008)
What does sp_decribe_cursor do? Has anyone seen such function or was it a typo ?


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.
Post #518742
Posted Tuesday, June 17, 2008 11:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 12:08 AM
Points: 84, Visits: 54
It should be "sp_describe_cursor" not "sp_decribe_cursor", you are right it is a typo.
Post #518762
Posted Wednesday, June 18, 2008 12:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 6,157, Visits: 8,418
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. :)

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. ;)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #518779
Posted Wednesday, June 18, 2008 1:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:48 AM
Points: 2,869, Visits: 371
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
Post #518803
Posted Wednesday, June 18, 2008 1:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 7:08 AM
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...
Post #518807
Posted Wednesday, June 18, 2008 2:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:47 AM
Points: 898, 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 :)).
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
Post #518824
Posted Wednesday, June 18, 2008 3:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 10:30 AM
Points: 4,656, Visits: 11,134
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
Post #518841
Posted Wednesday, June 18, 2008 3:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 19, 2009 1:54 PM
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?
Post #518850
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse