Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

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


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
kishore_peddi
Asked:
kishore_peddi
  • 4
  • 3
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi kishore_peddi,

Bydefault it is SCROLL

Cheers!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
David ToddSenior DBACommented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
kishore_peddiAuthor Commented:
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
 
kishore_peddiAuthor Commented:
David,

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

Do you agree on this? Thanks for your help!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
kishore_peddiAuthor Commented:
It is WIN-WIN race. Thanks a lot for you guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now