Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-10-31
8
Medium Priority
?
378 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 1800 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 200 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

916 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