Solved

Which type of cursor this is (KEYSET or FIREHOSE or DYNAMIC or STATIC ) ?

Posted on 2006-10-31
8
366 Views
Last Modified: 2006-11-18

USE Sample
GO

CREATE TABLE dbo.Source
(
   x  int,
   y  int
)
GO

INSERT INTO Source VALUES (1, 2)
GO

INSERT INTO Source VALUES (2, 3)
GO

INSERT INTO Source VALUES (3, 4)
GO

CREATE TABLE dbo.Dest
(
   tot  int
)
GO

-- Cursor Approach

/* Declare temporary variables */

DECLARE @x     int
DECLARE @y     int
DECLARE @tot   int

/* Declare the cursor */

DECLARE c1 CURSOR FOR

SELECT x, y FROM Source

/* Open the cursor */

OPEN c1

/* Perform the first fetch */

FETCH NEXT FROM c1 INTO @x, @y

/* Check to see if there are any more rows */

WHILE @@FETCH_STATUS = 0

BEGIN

     /* Do processing */
     
     SET @tot = @x + @y
     
     INSERT INTO Dest (tot) VALUES (@tot)
     
     /* If the previous fetch succeedsd, then get another row */

     FETCH NEXT FROM c1 INTO @x, @y

END

/* Close the cursor */  

CLOSE c1

/* De-allocate the cursor */

DEALLOCATE c1

GO

NOTE
====

can you please let me know, which type of this cursor it is ?

Is it

KEYSET or FIREHOSE or DYNAMIC or STATIC ?

Advance thanks for your time and patience.
0
Comment
Question by:kishore_peddi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17847580
Hi kishore_peddi,

Bydefault it is SCROLL

Cheers!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17847586
refer Books online
this is the syntax  



DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

0
 
LVL 35

Accepted Solution

by:
David Todd earned 450 total points
ID: 17847596
Hi,

From SQL 2000 BOL for DECLARE CURSOR

FORWARD_ONLY

Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.

I'd suggest that this indicats that the above cursor defaults to FORWARD_ONLY.

Why do you ask? Which version of SQL?

Regards
  David

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kishore_peddi
ID: 17847716
I have got 2 different statements. One is SCROLL and another one is FORWARD_ONLY. I am using SQL Server 2000. In my declaration

/* Declare the cursor */

DECLARE c1 CURSOR FOR
SELECT x, y FROM Source

i have not mentioned the type of cursor i am using. So what will be the default ? Can you please tell me which type of cursor i am using in this case ?

Advance thanks for your time and patience.
0
 

Author Comment

by:kishore_peddi
ID: 17847722
David,

I am using SQL Server 2000. According to BOL, i think so it will be FORWARD_ONLY. Am i correct ?
0
 

Author Comment

by:kishore_peddi
ID: 17847726
aneeshattingal,

Do you agree on this? Thanks for your help!
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 50 total points
ID: 17847781
kishore_peddi,
> Do you agree on this? Thanks for your help!

Yes, it was my mistake (Copy paster Error indeed ) , really sorry for that
:(
0
 

Author Comment

by:kishore_peddi
ID: 17847794
It is WIN-WIN race. Thanks a lot for you guys.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question