Re: How to use CURSOR with SELECT * ?

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/30/04 

Date: Mon, 30 Aug 2004 18:07:16 +0200

On Mon, 30 Aug 2004 15:54:11 +0100, Harag wrote:

(snip)
>
>Cool thanks, I got Aarons post and marked it to keep.
>
>BTW... what is the LOCAL for - I can't find this in BOL.
>
>Thanks
>
>Al.

Hi Al,

It's described in the article about DECLARE CURSOR. Copied text below.

LOCAL

Specifies that the scope of the cursor is local to the batch, stored
procedure, or trigger in which the cursor was created. The cursor name is
only valid within this scope. The cursor can be referenced by local cursor
variables in the batch, stored procedure, or trigger, or a stored
procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local
cursor back to the calling batch, stored procedure, or trigger, which can
assign the parameter to a cursor variable to reference the cursor after
the stored procedure terminates. The cursor is implicitly deallocated when
the batch, stored procedure, or trigger terminates, unless the cursor was
passed back in an OUTPUT parameter. If it is passed back in an OUTPUT
parameter, the cursor is deallocated when the last variable referencing it
is deallocated or goes out of scope.

GLOBAL

Specifies that the scope of the cursor is global to the connection. The
cursor name can be referenced in any stored procedure or batch executed by
the connection. The cursor is only implicitly deallocated at disconnect.

Note If neither GLOBAL or LOCAL is specified, the default is controlled
by the setting of the default to local cursor database option. In SQL
Server version 7.0, this option defaults to FALSE to match earlier
versions of SQL Server, in which all cursors were global. The default of
this option may change in future versions of SQL Server. For more
information, see Setting Database Options.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: noise words, @@ERROR, and stop and resume indexing
    ... these words at the beginning before sending them to the cursor. ... Looking for a FAQ on Indexing Services/SQL FTS ... >>Create table Noise ... >>> 1) check noise words inside stored procedure ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Error adding command properties for stored procedure call
    ... I see you are using the SQL Native Client for SQL Server ... A server cursor is not allowed on a remote stored procedure or stored ... The stored procedure called is a test one that simply SELECTs the name field ... int InitializeAndConnect; ...
    (microsoft.public.data.oledb)
  • Re: SQL stored procedure not working.
    ... If I do not use that, how can I get the total record count? ... >> I create a stored procedure and a asp page. ... Even if you could specify the cursor type there, ... > interested in the RETURN value, you do not need to use a Command object. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Error adding command properties for stored procedure call
    ... Microsoft OLE DB Provider for SQL Server ... properties to the command the execute call attempts to create a cursor. ... A server cursor is not allowed on a remote stored procedure or stored ...
    (microsoft.public.data.oledb)
  • Re: Case statement issue
    ... except with a cursor other than the default ... > I have taken the sample of paging using a stored procedure from the ... > case statement, then the error message. ... > The for loop also seems to be a problem. ...
    (microsoft.public.inetserver.asp.general)