Stored Procedure Issue

[inet]

Hi All,

I have this stored procedure:


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure usp_job_schedule_rep
as

declare @tlbkup varchar(8)
declare @sqlstmt nvarchar(500)

declare tlbkup_cursor cursor
for

select top 100 percent Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), tl.active_start_time), 6), 3,0, ':'), 6, 0, ':')), 100),7) as 'Transaction Log Backup'
from
(select name, job_id, active_start_time
from sysjobschedules
where name like 'TLBkup%') as tl
join
(select name, job_id
from sysjobs
where name like 'TL Backup%') as sjt
on sjt.job_id = tl.job_id
where sjt.name not like '%Maintenance%'
order by sjt.name

open tlbkup_cursor

fetch next from tlbkup_cursor into @tlbkup
while @@fetch_status = 0
begin

print @tlbkup

update job_schedule_rep
set tlbkup = @tlbkup


FETCH NEXT FROM tlbkup_cursor INTO @tlbkup

END


CLOSE tlbkup_cursor
DEALLOCATE tlbkup_cursor


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


When I execute it, the update statement populates the tlbkup column with the last value from the cursor which is 1:40PM 19 times. How can I modify it so the tlbkup column is populated with all 19 different values from the cursor?

[skhanal]

Do you get different values for

print @tlbkup

If all transaction log backups ran within a minute then you will get only 1:40PM

[inet]

Yes, I get 19 different values.

[rmiao]

Do you need where clause in your update statement? Or use join, otherwise you just update all rows 19 times.

[inet]

I got it. Thanks for your help.