[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Getting the records count from a cursor

StephenMcC

3/23/2007 4:26:00 PM

Hi All,

I'm new to cursors and I'm wondering if it's possible to get the record
count/no of rows from a cursor before/without having to loop through the
contents, similar to SELECT @COUNT=COUNT(id) FORM my_table.

Any help/advice much appreciated.

S.
11 Answers

David Portas

3/23/2007 4:49:00 PM

0

On 23 Mar, 16:26, StephenMcC <Stephen...@discussions.microsoft.com>
wrote:
> Hi All,
>
> I'm new to cursors and I'm wondering if it's possible to get the record
> count/no of rows from a cursor before/without having to loop through the
> contents, similar to SELECT @COUNT=COUNT(id) FORM my_table.
>
> Any help/advice much appreciated.
>
> S.

Do:

SELECT @COUNT=COUNT(id) FROM ...

Where ... represents whatever query result or table you want to count.
There's no reason to do that through a cursor. As always, avoid
cursors where you can.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

Tibor Karaszi

3/23/2007 5:21:00 PM

0

A general recommendation is to avoid cursors when possible (for performance and code simplicity
sake). However, if you need to use a cursor and want to know number of rows after OPEN, check out
the @@CURSOR_ROWS function (but make sure you read in BOL about it first).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"StephenMcC" <StephenMcC@discussions.microsoft.com> wrote in message
news:1EE6732F-8C28-4EEA-BD06-445A41348211@microsoft.com...
> Hi All,
>
> I'm new to cursors and I'm wondering if it's possible to get the record
> count/no of rows from a cursor before/without having to loop through the
> contents, similar to SELECT @COUNT=COUNT(id) FORM my_table.
>
> Any help/advice much appreciated.
>
> S.

Steve Dassin

3/23/2007 9:25:00 PM

0

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:OUUEp%23WbHHA.1300@TK2MSFTNGP02.phx.gbl...
>A general recommendation is to avoid cursors when possible.....and code
>simplicity sake.

That, my friend, is the height of intellectual arrogance. As usual
everything here is upside down. The sql dyslexia is the norm. Application
developers really like common sense, like cursors. Now your telling them
it's the simplicity of sql that their missing. You might as well try
convincing them the real problem birds have is their attempt to fly. It is
the cursor that simplifies and clarifies. It is the natural way of problem
solving. The real problem is how poorly they are implemented.
Avoid cursors for poor performance and for simplicities sake: sorta like
saying the real problem people have this their attempt to walk -:)

very best,


Tibor Karaszi

3/23/2007 9:37:00 PM

0

I knew somebody was going to bite, just after I posted my comments. Perhaps I should have qualified
a bit. Yes, whether avoiding cursor simplifies the solution or not varies of course. But surely you
agree that beginners of SQL can sometimes overuse cursors, while after learning SQL some of the uses
will be less code in SQL. (And, yes I know about Tutorial-D (?), but so far, SQL is what we have to
live with.)

:-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"Steve Dassin" <steve@nospamrac4sql.net> wrote in message
news:uCmPkHZbHHA.4888@TK2MSFTNGP06.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message
> news:OUUEp%23WbHHA.1300@TK2MSFTNGP02.phx.gbl...
>>A general recommendation is to avoid cursors when possible.....and code simplicity sake.
>
> That, my friend, is the height of intellectual arrogance. As usual everything here is upside down.
> The sql dyslexia is the norm. Application developers really like common sense, like cursors. Now
> your telling them it's the simplicity of sql that their missing. You might as well try convincing
> them the real problem birds have is their attempt to fly. It is the cursor that simplifies and
> clarifies. It is the natural way of problem solving. The real problem is how poorly they are
> implemented.
> Avoid cursors for poor performance and for simplicities sake: sorta like saying the real problem
> people have this their attempt to walk -:)
>
> very best,
>

Hugo Kornelis

3/24/2007 12:06:00 AM

0

On Fri, 23 Mar 2007 14:24:56 -0700, Steve Dassin wrote:

>"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
>message news:OUUEp%23WbHHA.1300@TK2MSFTNGP02.phx.gbl...
>>A general recommendation is to avoid cursors when possible.....and code
>>simplicity sake.
>
>That, my friend, is the height of intellectual arrogance. As usual
>everything here is upside down. The sql dyslexia is the norm. Application
>developers really like common sense, like cursors. Now your telling them
>it's the simplicity of sql that their missing.

Hi Steve,

No, my friend, you are completely missing the point here.

If you have an algorithmic problem, like processing individual rows in
order from a file, you use an algorithmic programming language, which
requires it's user/programmmer to think in algorithmic structures
(if/then/else blocks, do/while loops, etc)

If you have a list-related problem, you use a list-oriented language
such as LISP, which requires a completely different way of thinking
about the problem and the solution.

If you want to do simulations, you'll probably use a specialized
language for that purpose, such as SIMULA, which requires yet another
way of thinking to be used optimally.


And if your problem is set-based, the obvious choice is a set-oriented
language, such as SQL, and a set-based mindset to find the best code for
the problem.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Steve Dassin

3/24/2007 1:31:00 AM

0

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:uurouNZbHHA.4656@TK2MSFTNGP03.phx.gbl...
>.
(And, yes I know about Tutorial-D (?), but so far, SQL is what we have to
> live with.)

Sir, I think you left a little opening for sunshine so
permit me to drive a truck thru it -:)
When an MVP takes an op to task for not properly normalizing
their data and ten minutes latter posts code to show how to
remove duplicates, one wonders how many people will continue
to scratch their heads and ask what's wrong with this
picture? How long will the industry tolerate an
intellectually bankrupt situation? You would think the shear
weight of hypocrisy demonstrated in this ng would have lead
to its collapse a long time ago. But errors of commission
are indistinguishable from errors of omission. I can't get
them all :( :).
I am really not an sql server basher (I reserve my best for
excoriating MS for Vista). To borrow a phrase from George
Wallace (and recently poined out to me:), fundamentally
their ain't a dimes worth of difference between sql server,
Oracle, DB2 yada yada yada. But this is a MS ng, so it
doesn't make a lota sense to pick on Oracle. If IT is ever
going to extract itself from the sorry mess its in it's
going to start with people on ng's like this getting off
their arse, start being 'honest' and pointing the way. And I
can assure you that Tutorial-D lives and breaths with
Alphora's Dataphor (D4). I have been working with it for a
while and can tell you that once you get 'it' you will see
the childishness of sql. It's the getting people to dive in
that's the hard part:) I can also tell you that it is not an
inexpensive learning experience. It will take a net
developer months to get a real feel for it. It will take an
sql programmer a year:) Unfortuneately sql programmers have
no real grasp of a strong typed system. But they will get
it. It is also unfortunate that Date is such a pedantic
writer. Tutorial-D is not an easy read but working with it
is a whole nother story. And I would also point out that you
are not giving up sql. Assuming sql server as the data
respository, you can pass thru whatever sql you want. When
the result is passed back thru the cli you can treat
it/manipulate it as any other D4 variable.
The idea of table types, row types, intelligent 'keys', all
the things that sql can only tacitly offer are there. Cursor
vs. set based solutions will take on a new meaning.
I'll be posting some real indepth D4 code soon. Including
cursors :)
Lets give 'relational' its due.

best,
steve

Dataphor @
www.alphora.com

www.beyondsql.blogspot.com

Steve Dassin

3/24/2007 1:42:00 AM

0

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:oiq8035f7vte8am9khg53ha1csv4246tvk@4ax.com...
> Hi Steve,
>
> No, my friend, you are completely missing the point here.

I think your politely telling me to set down :( :)

best,
steve

Steve Dassin

3/27/2007 10:56:00 PM

0

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:uurouNZbHHA.4656@TK2MSFTNGP03.phx.gbl...
>>A general recommendation is to avoid cursors when possible (for
>>performance and code simplicity sake).
>I knew somebody was going to bite, just after I posted my comments.

I guess you were wrong about being bitten. That's ok, it puts us all in good
company -:)

best,
steve


StephenMcC

3/27/2007 11:02:00 PM

0

Hi Guys,

Thx for ur comments, seems I've opened up an interesting debate!

Mmm, I'm new to cursors but not to SQL / SQL Server, I've been well educated
that cursors are, well, not great when it comes to performance, so I've
always been told to stay clear, and I would if I could...

But my hand is forced in this case, I'm currently working with Sybase which
has somewhat similar to SQL Server, they used to be quite closely related
some versions ago, but they have since parted company big time.

I would normally do what I have to via #temp tables, but Sybase doesn't like
this, due to a DDL In Tran Issue as we're working with Java/JDBC, MDAC never
used to mind! So I might look into the @@CURSOR_ROWS function, but not
because I want to...

I reckon if I HAVE to use cursors, then Iâ??m gonna make then earn there keep,
make it do all the work (ie: get my count). I also plan, when I've gotten all
I want from the cursor, to release this and get the â??hell-outta-dodgeâ?? asap!

Again, thanks for ur comments.

StephenMcC.

"Steve Dassin" wrote:

> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
> news:oiq8035f7vte8am9khg53ha1csv4246tvk@4ax.com...
> > Hi Steve,
> >
> > No, my friend, you are completely missing the point here.
>
> I think your politely telling me to set down :( :)
>
> best,
> steve
>
>
>

StephenMcC

3/28/2007 12:16:00 AM

0

Further on my last note, seems @@CURSOR_ROWS is not supported in the version
of Sybase we're using, 12.5 me thinks (why am I not surprised)!

However, a merge of ur two suggestions seems to be working:
DECLARE CurIns CURSOR FOR SELECT ID, COUNT(ID) 'RecCount' FROM My_Table
WHERE ID LIKE '1524002%'

FETCH CurIns INTO @RowID, @RowCount

Thx,

StephenMcC.

"Steve Dassin" wrote:

> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
> news:oiq8035f7vte8am9khg53ha1csv4246tvk@4ax.com...
> > Hi Steve,
> >
> > No, my friend, you are completely missing the point here.
>
> I think your politely telling me to set down :( :)
>
> best,
> steve
>
>
>