Get records count from SQL cursor

Hi experts,
I have created a SQL cursor for records processing in a stored procedure. I
probably can use the @.@.Cursor_Rows function in order to obtain total rows of
record contained inside the cursor. But once I declare the cursor as
FAST_FORWARD, it always return me -1. I need to declare the cursor as
FAST_FORWARD as it really helps in tuning the performance. Else my stored
procedure will take longer time to execute.
Any other way I can use to get the total records being returned? I've tried
to signal another SQL statement to perform the records count but this seems
to create redundant overhead. I believe if I can do anything to existing
cursor without having extra Select Count statement, it would help to reduce
unnecessary processing and shorten the overall time required.
Really appreciate for any advice or suggestion. Thanks a lot.Can you test perfoermance between your current cursor declaration and the on
e
I am posting?
declare my_cursor cursor
local
forward_only
static
read_only
for
...
AMB
"LBT" wrote:

> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've trie
d
> to signal another SQL statement to perform the records count but this seem
s
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to reduc
e
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||If performance is a concern for you then how about getting rid of the
cursor altogether? Cursors are rarely a good idea.
David Portas
SQL Server MVP
--|||Thanks for the suggestion. The time taken is still approximately equal to th
e
cursor which is declared without those keywords. And I can't declare the
cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I forgo
t
to write out this concern in my previous post).
"Alejandro Mesa" wrote:
> Can you test perfoermance between your current cursor declaration and the
one
> I am posting?
> declare my_cursor cursor
> local
> forward_only
> static
> read_only
> for
> ...
>
> AMB
>
> "LBT" wrote:
>|||You can use select count(*)... yada,,, the optimizer can use the index
entries instead of having to read the data ( if there is a supporting
index.)
You can also use select @.@.rowcount AFTER the query...
You have to open the cursor before the rowcount info is available for
cursors...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
> I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
> of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've
> tried
> to signal another SQL statement to perform the records count but this
> seems
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to
> reduce
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||I performed the following test but @.@.rowcount return me 0. Please check for
me if there is something wrong with the code. Thanks
---
declare @.temp varchar(50)
declare my_cursor cursor fast_forward for
select column01 from table01
open my_cursor
select @.@.rowcount
fetch my_cursor into @.temp
while @.@.fetch_status = 0
begin
print @.temp
fetch my_cursor into @.temp
end
close my_cursor
deallocate my_cursor
----
--
"Wayne Snyder" wrote:

> You can use select count(*)... yada,,, the optimizer can use the index
> entries instead of having to read the data ( if there is a supporting
> index.)
> You can also use select @.@.rowcount AFTER the query...
> You have to open the cursor before the rowcount info is available for
> cursors...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
>
>|||I do agree that cursor is definately not a good idea to be used...I did thin
k
of using table variable previously but since I need to use dynamic SQL and
table variable is not supported to be used together with dynamic SQL...any
other way to get rid of cursor? Will temp table use the equivalent overhead
as cursor? I think i need to temporarily store records (based on passed-in
filtering criteria) to somewhere else so that i'm able to loop the records
and perform further analysis. My stored procedure will return one summarized
result based on passed-in filtering criteria.
"David Portas" wrote:

> If performance is a concern for you then how about getting rid of the
> cursor altogether? Cursors are rarely a good idea.
> --
> David Portas
> SQL Server MVP
> --
>|||> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
Who said that?
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.c cursor
set @.sql = N'set @.c = cursor local forward_only static read_only for select
orderid, orderdate from orders where orderdate >= ''19960101'' and orderdate
< ''19970101''; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c output
if cursor_status('variable', '@.c') >= 0
begin
print @.@.cursor_rows
close @.c
deallocate @.c
end
go
I am not advocating for cursors.
AMB
"LBT" wrote:
> Thanks for the suggestion. The time taken is still approximately equal to
the
> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
>
> "Alejandro Mesa" wrote:
>|||Your reply just poses more questions upon questions. Why dynamic SQL?
Dynamic SQL, like cursors, is something you should aim to avoid, or at
least minimize. Why "loop the records"? Or for that matter, why
"temporarily store records"? (BTW the term "rows" is generally
preferred to "records" in RDBMS).
In short, the best way to get help is to describe *what* you want to do
rather than *how* you think you should go about it. Tell us what your
goal is and show us your data structure as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||>> My stored procedure will return one summarized result based on
passed-in filtering criteria. <<
Get a **basic** software engineering book and look up coupling and
cohesion. In a properly designed module, you do not pass in criteria.
That module would have no cohesion. This is far more basic than SQL;
this is how to be any kind of programmer.
Let's go back to square one and find out what you are trying to do and
then we can look for a set-based, relational approach that will give
you a maintainable procedure. You might also want to consider taking
college courses on software engineering, then learn databasess and data
modeling.