Updating records with TSQL cursor and WHERE CURRENT OF

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Me (heruti_at_lycos.com)
Date: 11/19/04 

Date: 19 Nov 2004 13:44:33 -0800

Hi all,

I'm using WHERE CURRENT OF and a forward only cursor on SQL 2000
stored procedure, out of laziness really (instead of using ADO code),
since all the stored procedure functionality is already in place in
our ASP code (with commit rollback etc). I am using this stored
procedure to add (concatenate) incremental index numbers to one of the
text fields, CURRENT_DESC
in a table (can't be done in transactional SQL as far as I can tell).

what I wanted to ask, I am not sure whether updating data with a
cursor is still under the protection of commit and rollback...
i.e., will a rollback undo these changes as well?
I should mention that our commits and rollbacks are implemented from
outside the stored procedures, in the calling ASP code environment,
using the connection object which executed the stored procedure.

such a stored procedure I intend to use for the updating cursor
functionality (code below), without commit rollback statements in this
code. (as said, they are already implemented in the calling ASP code).

I read some conflicting opinions regarding bugs and problems with this
that seem to indicate that commit and rollback only 'cover'
conventional SQL commands.

Thank you all for your support.
I feel increasingly guilty about all the free help I got from public
web forums, and one day (so I say each time...) I will do something
about it...

SET @COUNTER = 1

OPEN PROJECT
FETCH NEXT FROM PROJECT INTO @CURRENT_PROJ_ID,@CURRENT_DESC
WHILE (@@FETCH_STATUS = 0) BEGIN
 
UPDATE PROJECT SET PROJ_DESC = PROJ_DESC +
CONVERT(VARCHAR(2),@COUNTER) WHERE
CURRENT OF PROJECT

 SET @COUNTER = @COUNTER + 1

 FETCH NEXT FROM PROJECT INTO @CURRENT_PROJ_ID,@CURRENT_DESC

END

CLOSE PROJECT
DEALLOCATE PROJECT

Relevant Pages

  • Re: Transactional Processing
    ... SQL Server MVP ... Begin transaction, rollback, Commit ...
    (microsoft.public.sqlserver.server)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • Re: Stored Procedures - Patterns and Practices
    ... >published the reasoning behind its opinions. ... I disagree that the debate in SQL Server related discussion forums ... If the natural key is long or spans too many ... I want to call a stored procedure that adds a customer ...
    (microsoft.public.sqlserver.programming)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... "Active Server Pages error 'ASP 0113' ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)
  • Re: XML vs SQL Server
    ... The built in factory assumes a common syntax among the ... So as long as the sql can be shared, ... procedures for Sql Server and stored procedure for Oracle? ... Oracle supports stored procedure overloads, ...
    (microsoft.public.dotnet.languages.csharp)