Re: Using Cursors
From: peterDavey (peter.davey_at_austin.org.au)
Date: 01/27/05
- Next message: Firmansyah: "Re: Crosstab Query"
- Previous message: zaratino: "Re: "Macro" statement"
- Maybe in reply to: Tibor Karaszi: "Re: Using Cursors"
- Next in thread: Nickl: "Re: Using Cursors"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 27 Jan 2005 12:40:56 +1100
Well, it seems I've stirred up a hornet's nest with my cursor question. I'm
finding the discussion valuable in my circumstances (moving from Oracle to
SQL Server). I'd like to make a few points:
I've been using Oracle for 15 years and have a philosophy of 'horses for
courses'. Sometimes pure SQL works best, sometimes cursors. Generally, I
if I need to start inserting and updating into temporary tables I'll look at
the procedural (PL/SQL) alternative. Inserting into and updating temporary
table is resource intensive (lots of disk writing). Using cursors I find I
can minimise the amount of database writes that are necessary.
When I first started using Oracle PL/SQL didn't exists and I did everything
using 'set based SQL' (I'm not sure that there's any other sort of SQL).
When PL/SQL arrived Oracle programmers discovered that they could convert
many complex, unwieldy SQL processes into simpler, more elegant (and
efficient) procedural code.
I gather that many of the people who have replied don't have any significant
Oracle experience and are therefore assuming that Oracle works the same as
SQL Server. This is probably not a reasonable thing to do. I'm trying to
keep an open mind in my approach and am willing to learn from the SQL Server
experts. Based on what I've read here I'll be comparing cursor vs set based
approaches. The problem I have is that I have limited time and resources to
complete my conversion and initially I see the easiest way to do it would be
to convert my existing processes rather than re-creating them from scratch.
I know I'll probably get howled down for saying this but based on my limited
experience so far I'd have to say that T-SQL provides signicantly inferior
procedural functionality. Perhaps this is one of the reasons the procedural
option isn't used as much in SQL Server shops.
Because of the obvious interest in this subject, in the next couple of weeks
I'll create a new post with what I hope will be a fairly objective look at
my experience. Hopefully it will at least be helpful for anyone else moving
from Oracle to SQL Server.
cheers
peterDavey
"peterDavey" <peter.davey@austin.org.au> wrote in message
news:eTNJlNgAFHA.3376@TK2MSFTNGP12.phx.gbl...
> 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
>
>
- Next message: Firmansyah: "Re: Crosstab Query"
- Previous message: zaratino: "Re: "Macro" statement"
- Maybe in reply to: Tibor Karaszi: "Re: Using Cursors"
- Next in thread: Nickl: "Re: Using Cursors"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
