Solved

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

Posted on 2006-10-31
8
323 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
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Hi kishore_peddi,
>
Bydefault it is SCROLL

Cheers!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
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
 

Author Comment

by:kishore_peddi
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:kishore_peddi
Comment Utility
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
Comment Utility
aneeshattingal,

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

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 50 total points
Comment Utility
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
Comment Utility
It is WIN-WIN race. Thanks a lot for you guys.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now