@@FETCH_STATUS in nested cursors

[iccsi]

I read SQL server 200 document for nested cursors.

It uses @@FETCH_STATUS variable to check FETCH STATUS for inner and
outer cursor.


I just wonder does SQL server 2000 know which cursor fetch status?
Do I need use different variable for different cursors?
How many layer nested cursors SQL server 2000 support?


Your help is great appreciated,


iccsi

[Hugo Kornelis]

On Tue, 18 Oct 2011 10:23:33 -0700 (PDT), iccsi <inu...@gmail.com>
wrote:

Hi iccsi,

You left off the last number of the SQL Server version, but that does
not matter in this case, as my answer applies to all versions:

1. Try to avoid cursors. The SQL Server implementation of cursors is
slow, and in 99% of cases, you'll get a tremendous gain by replacing a
cursor with set-based logic. If you need help with that, post the
layout of your tables (as CREATE TABLE statements), some sample data
(as INSERT statements), a description of the problem and your existing
code (if you have any). Any of the experts who hang around here will
gladly help you find a better alternative.

2. Definitely try to avoid nested cursors. Start with my arguments
after point 1. Raise that to the power of itself. That's nested
cursors for you.

3. To answer your first question, @@FETCH_STATUS always returns the
status after the last executed FETCH statement. If you use multiple
cursors, it's up to you to track which cursor that was on.

4. And to answer your question on maximum cursor nesting: I don;t want
to know, and neither do you. See above for the reason.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

Hugo Kornelis (hu...@perFact.REMOVETHIS.info.INVALID) writes:
> 4. And to answer your question on maximum cursor nesting: I don;t want
> to know, and neither do you. See above for the reason.

And I add to that, that the question is not meaningful. Possibly there is
a limit of how many cursors you can have open at the same time. Note the
small distinction: just because you have two cursors open, does not mean
that they are nested. I have occasionally written code where run two cursors
in parallell to merge data from different sources..


--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[--CELKO--]

The SQL Server model for transactions, cursors, etc. is to have a
global flags that are set by an event. There is no scoping or local
flags. You have to catch each flag as it is set and use your own local
variable. I thikn the limit on cursors is 32, but I ghave no idea
where I got that number.

Now, never use cursors. They cannot help the optizer, they do not
port, they run 1-3 orders of magnitude slower than pure declarative
SQL. I have written five of them in my career; three of them could
have ben avoided if we had had CASE expressions in those days.

[iccsi]

On Oct 18, 3:47 pm, Hugo Kornelis

Thanks for helping and information,
My need create a pivot table report by date which only know at run
time.
I learn dynamic SQL from Erland web site.
Because there are 18 elements I need to create, I use UNION to union
all 18 stored procedures from client application to get the result.

I thought that if I can have all 18 elements in one stroed procedure
will perform better than union 18 stored procedures from client.

If I want to put all 18 stored procedures in one stored procedure then
I need nested cursors to produce the result.

Since nested cursors do not help performance then I just leave 18
stored procedures using dynamic SQL and union them from client
application.

Please let me know if client applicaiton union 18 stored procedures do
not perform better than nested cursors.

Thanks again for helping,


iccsi

[Erland Sommarskog]

iccsi (inu...@gmail.com) writes:
> Thanks for helping and information,
> My need create a pivot table report by date which only know at run
> time.
> I learn dynamic SQL from Erland web site.
> Because there are 18 elements I need to create, I use UNION to union
> all 18 stored procedures from client application to get the result.
>
> I thought that if I can have all 18 elements in one stroed procedure
> will perform better than union 18 stored procedures from client.
>
> If I want to put all 18 stored procedures in one stored procedure then
> I need nested cursors to produce the result.
>
> Since nested cursors do not help performance then I just leave 18
> stored procedures using dynamic SQL and union them from client
> application.
>
> Please let me know if client applicaiton union 18 stored procedures do
> not perform better than nested cursors.

Without seeing the code, I don't want to make a dare to say what is
better. I'm not sure that I understand "client application union 18 stored
procedures"; it does not really parse for me.

[iccsi]

> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx- Hide quoted text -
>
> - Show quoted text -

Basically, it is a internal evaluation application which contains 18
elements.
every element comes from different source.
I need create a weekly progress report for management
Since I do not know the date range until run time, I need use dynamic
SQL to create pivot result using stroed procedures.
I can not access a stored procedure from a stored procedure.

I use a client application, MS Access, to run an UNION query against
my SQL server database.

The UNION query like following:

Select * from myStoredProc1
UNION
Select * from myStoredProc2
UNION
Select * from myStoredProc3
......

every stroed procedure is one element result.

This is one of the reason I want to use nested cursor to create a
stored procedures to get my result.

[Erland Sommarskog]

iccsi (inu...@gmail.com) writes:
> Basically, it is a internal evaluation application which contains 18
> elements.
> every element comes from different source.
> I need create a weekly progress report for management
> Since I do not know the date range until run time, I need use dynamic
> SQL to create pivot result using stroed procedures.
> I can not access a stored procedure from a stored procedure.
>
> I use a client application, MS Access, to run an UNION query against
> my SQL server database.
>
> The UNION query like following:
>
> Select * from myStoredProc1
> UNION
> Select * from myStoredProc2
> UNION
> Select * from myStoredProc3

> .......

>
> every stroed procedure is one element result.
>
> This is one of the reason I want to use nested cursor to create a
> stored procedures to get my result.

Just because a date range is unknown, that in it self does not call
for dynamic SQL. But I guess there is more into it.

If the procedures produce dynamically shaped result sets, the above
approach seems better to me than the cursor. An alternative might
to be compile all data before doing the pivot.

[--CELKO--]

>> Basically, it is a internal evaluation application which contains 18 elements. <<

So you have a table with 18 or more of the appropriate data types.

>> every element comes from different source. <<

So, you have 18 UPDATE statements with edits as needed

>> I need create a weekly progress report for management <

So you have a report period calendar table. Use the ISO "yyyyWdd" for
the weeks

>> Since I do not know the date range until run time, I need use dynamic SQL<<

Why not pass a parameter with the ISO week in it? You know all the
weeks that can ever exist already.

>> The UNION query like following: <<

Do not use SELECT * in production code. Why are you putting 18
different things in one column? Where is the date used to group them
into the weeks? Your narrative makes no sense.

[iccsi]

Thanks for the message and help,
The SELECT * is example code, not the real code for the the query.
The result data type are float.
All the data from different sources and import in to the system.
I let user choose day range, so I only know the day at ryn time.

thanks again,

iccsi

[iccsi]

> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx- Hide quoted text -
>
> - Show quoted text -

I learn from your website that the solution for unknown day range is
dynamic SQL. Am I right?
Any other solution for this?
I tried to use nested cursor, because I want to consolidate 18 stroed
procedures to one.
According to your message, 18 stored procedures is a better solution
than nested cursors.

Please let me know if I am wrong,

Thanks a million for your help,


iccsi

[Erland Sommarskog]

iccsi (inu...@gmail.com) writes:
> I learn from your website that the solution for unknown day range is
> dynamic SQL. Am I right?

Depends on what you mean with "unknown day range". If all you want is data
beteween @startdate and @enddate, there is no need for dynamic SQL. If you
want one column per day, you need to apply some trickery.

> Any other solution for this?

You could assume a maximum number of columns, insert into a fixed table,
drop the superfluous columns, and rename the remaining the actual date.

> I tried to use nested cursor, because I want to consolidate 18 stroed
> procedures to one.
> According to your message, 18 stored procedures is a better solution
> than nested cursors.

Does each of these procedure include a pivot of the same date range? In such
case, why not assemble data first in a normal table, and then pivot at the
end.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[iccsi]

> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...

> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks a million for helping,

iccsi

[iccsi]

On Oct 21, 3:19 am, Erland Sommarskog <esq...@sommarskog.se> wrote:

> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...

> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for the message,
If I use fixed columns tables and rename the actual date then I only
need one dinamic SQL instead of 18,because I have to use Alter table
whicvh still needs pass column name using dynamic SQL.
Am I right?

Please let me know if I am wrong,

Thanks again for helping,


iccsi

[Erland Sommarskog]

iccsi (inu...@gmail.com) writes:
> If I use fixed columns tables and rename the actual date then I only
> need one dinamic SQL instead of 18,because I have to use Alter table
> whicvh still needs pass column name using dynamic SQL.
> Am I right?
> Please let me know if I am wrong,

To rename the columns, you don't even need dynamic SQL. You can use
sp_rename.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx