Using Cursors

From: peterDavey (peter.davey_at_austin.org.au)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 21:58:37 +1100

G'day,
I'm moving from Oracle top SQL Server and starting recode my Oracle stored
procedures into T-SQL. I have a procedure in Oracle that opens a cursor,
loops through each record and does an update or insert where appropriate.

I've just created my first cursor in T-SQL. It runs OK in Query Analyser
but the problem is that it in the output pane it displays a separate query
output for each record returned by the cursor. I don't want to it to output
anything other than some summary stats afte it's completed. The code is
below: I've excluded the SELECT statement because it's BIG.

DECLARE curEpisode SCROLL CURSOR FOR
    SELECT
            some stuff ...

OPEN curEpisode

-- Perform the first fetch.
FETCH NEXT FROM curEpisode

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM curEpisode
    --... do stuff ....
 SET @counter = @counter + 1
END

CLOSE curEpisode
DEALLOCATE curEpisode
SELECT @counter
GO

Thanks in advance for any help.

cheers
peterDavey
Austin Health
Melbourne

Relevant Pages

  • Re: Using Cursors
    ... it seems I've stirred up a hornet's nest with my cursor question. ... finding the discussion valuable in my circumstances (moving from Oracle to ... SQL Server). ... > DECLARE curEpisode SCROLL CURSOR FOR ...
    (microsoft.public.sqlserver.programming)
  • RE: Using Cursors
    ... > I'm moving from Oracle top SQL Server and starting recode my Oracle stored ... I have a procedure in Oracle that opens a cursor, ... > but the problem is that it in the output pane it displays a separate query ... > DECLARE curEpisode SCROLL CURSOR FOR ...
    (microsoft.public.sqlserver.programming)
  • Re: Using Cursors
    ... There's no doubt that in Oracle cursors are invaluable. ... the data with separate SQL statments to acheive what I want. ... convert that I'm sure I couldn't do without a cursor. ... > DECLARE curEpisode SCROLL CURSOR FOR ...
    (microsoft.public.sqlserver.programming)
  • fetching entire row in cursor
    ... i need to fetch to cursor and return entire row to put it in variable not in ... (like %rowtype in oracle) ...
    (microsoft.public.sqlserver.programming)
  • Re: ORATCL help needed!
    ... set cursor ... Wow, An Oratcl 3.3. ... It is coded with the the OCI layer released with Oracle ... Oracle instant client is available for many many platforms, ...
    (comp.lang.tcl)
Loading