npyata (5/2/2013)
Here is the code. This is my first step in the process and after this seq_no update I need to update some other columns based on this seq_no.USE [TESTDB]
GO
/****** Object: StoredProcedure [dbo].[sp_upd_match_eventlog] Script Date: 05/02/2013 11:17:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Author,Narender Pyata
-- Create date: 04/30/2013>
-- Description:Update match_event_log table log_seq_no
-- =============================================
ALTER PROCEDURE [dbo].[sp_upd_match_eventlog]
-- Add the parameters for the stored procedure here
AS
declare
@vr_seqno int,
@log_id int
declare log_data cursor for
select log_id
from [TESTDB].[dbo].[match_event_log]
where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'
order by log_id;
open log_data
fetch next from log_data
into @log_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Update statements for procedure here
BEGIN TRANSACTION Proc_IN
set @vr_seqno = @vr_seqno + 1;
Update [CAMINO].[dbo].[match_event_log]
set log_seqno = @vr_seqno
where log_id = @log_id;
COMMIT TRANSACTION Proc_IN;
END;
besides being slow, it looks to me like when it's all done, the column you want to have sequential integer values will be NULL, because you never assigned a value to @vr_seqno .
offhand i think this would do it in a single shot, but i'm looking for my friend Sean's matching post to be sure:
Update [CAMINO].[dbo].[match_event_log]
set log_seqno = vr_seqno
FROM (select
row_number() over (order by log_id,log_timestamp_dt) AS vr_seqno,
log_id
from [TESTDB].[dbo].[match_event_log]
where log_timestamp_dt between '2010-01-01 00:00:00.000' and '2010-02-28 00:00:00.000'
) MyData
where log_id = MyData.log_id;
Lowell