Updating table with looped stored proc using cursor

[SteveW]

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

[DaveSatz]

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
>