Updating table with looped stored proc using cursor


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Updating table with looped stored proc using cursor

Hybrid View

  1. #1
    SteveW Guest

    Updating table with looped stored proc using cursor


    Hello folks,

    Here's the problem:

    I have a temporary table (basketuploads) with jobid, filename, userid. I
    have a main table (jobs) with jobid, userid, reportname, jobstatus.

    I need to update the main table by setting jobs.reportname = basketuploads.filename,
    jobs.jobstatus = '6' where jobs.jobid = basketuploads.jobid and jobs.userid
    = basketuploads.userid
    I then delete all records for that userid.

    The problem is that I need to loop thru the table. How do I do this with
    a cursor?

    I have the following stored proc:

    ==================

    CREATE PROCEDURE AdminBasketClearDetails
    @SessionID VarChar(50)

    AS

    set nocount on

    declare @userid varchar(50)
    declare @labID Int


    set @userid = (select locationemail from userlog where sessionid = substring(@SessionID,1,40))


    update Jobs
    set JobStatus = 6

    where JobID
    in (
    select JobID
    from BasketUploads
    )

    Delete From BasketUploads
    Where UserId = @UserID
    GO

    ======================

    I'd appreciate it greatly if you could help with this one.

    Cheers,

    Steve


  2. #2
    DaveSatz Guest

    Re: Updating table with looped stored proc using cursor

    why a cursor ? something like:

    BEGIN TRANSACTION

    update Jobs
    set JobStatus = 6
    , reportname = basketuploads.filename
    FROM jobs j
    JOIN BasketUploads b ON j.JobID = b.JobID
    Where b.UserId = @UserID

    if @@error <> 0
    begin
    ROLLBACK TRANSACTION
    return
    end

    Delete From BasketUploads
    Where UserId = @UserID
    GO

    if @@error <> 0
    begin
    ROLLBACK TRANSACTION
    end
    else
    COMMIT TRANSACTION

    --
    HTH,
    David Satz
    Principal Software Engineer
    Hyperion Solutions
    ->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------
    "SteveW" <stephen.white@perseus.co.uk> wrote in message
    news:3b546690$1@news.devx.com...
    >
    > Hello folks,
    >
    > Here's the problem:
    >
    > I have a temporary table (basketuploads) with jobid, filename, userid. I
    > have a main table (jobs) with jobid, userid, reportname, jobstatus.
    >
    > I need to update the main table by setting jobs.reportname =

    basketuploads.filename,
    > jobs.jobstatus = '6' where jobs.jobid = basketuploads.jobid and

    jobs.userid
    > = basketuploads.userid
    > I then delete all records for that userid.
    >
    > The problem is that I need to loop thru the table. How do I do this with
    > a cursor?
    >
    > I have the following stored proc:
    >
    > ==================
    >
    > CREATE PROCEDURE AdminBasketClearDetails
    > @SessionID VarChar(50)
    >
    > AS
    >
    > set nocount on
    >
    > declare @userid varchar(50)
    > declare @labID Int
    >
    >
    > set @userid = (select locationemail from userlog where sessionid =

    substring(@SessionID,1,40))
    >
    >
    > update Jobs
    > set JobStatus = 6
    >
    > where JobID
    > in (
    > select JobID
    > from BasketUploads
    > )
    >
    > Delete From BasketUploads
    > Where UserId = @UserID
    > GO
    >
    > ======================
    >
    > I'd appreciate it greatly if you could help with this one.
    >
    > Cheers,
    >
    > Steve
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center