Solved

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

Posted on 2006-10-31
8
335 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
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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 36
Using Aggregate Functions to Count 3 34
Getting max record but maybe not use Group BY 2 18
Help Parsing a String with SQL Syntax 23 32
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

864 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

23 Experts available now in Live!

Get 1:1 Help Now