Declare @LocalError integer
declare @CURRENT_DATETIME datetime
declare @LocalPackageName char(50)
declare @LocalProcessName char(50)
declare @LocalVersion integer
DECLARE @Cost_Centre_Number integer
DECLARE @Aggregation_Completed_Date integer
DECLARE @Stopped_Flag integer
DECLARE @Step_Start datetime
DECLARE @Step_End datetime
declare @Process_From_date integer
DECLARE @Target_date integer

SELECT @Step_Start = getdate()
select @LocalPackageName = 'AGGREGATION'
Select @LocalProcessName = 'Process_Weekly_Aggregation_Queue'
select @LocalVersion = 1
select @Stopped_Flag = 0

update [ETL_SERVER].ETlControl.dbo.TDL_TASKS 
set STOP = 0
where PROCESSNAME = 'AGGREGATION_Q_PROCESSOR'

EXECUTE DW_WRITE_LOG	
	@Process = @LocalProcessName,
	@Package = @LocalPackageName,
	@Message = 'Process Started',
	@Start = @Step_Start,
	@VERSION = @LocalVersion, 
	@Keep = N,
	@Error = @LocalError

declare cu_WEEKLY_Aggregation_Queue cursor FOR 
SELECT * FROM WEEKLY_AGGREGATION_PROCESS_QUEUE2
where Aggregation_Completed_Date IS NULL 
ORDER BY Cost_Centre_Number
FOR UPDATE

open cu_WEEKLY_Aggregation_Queue
fetch next from cu_WEEKLY_Aggregation_Queue INTO @Cost_Centre_Number, @Target_date , @Aggregation_Completed_Date
while @@fetch_status = 0 and @Stopped_Flag = 0 
	BEGIN
		SELECT @Step_Start = getdate()
		EXECUTE DW_WRITE_LOG	
			@Process = @LocalProcessName,
			@Package = @LocalPackageName,
			@Message = 'Start Loop',
			@Start = @Step_Start,
			@VERSION = @LocalVersion, 
			@Keep = N,
			@Cost_Centre_Number = @Cost_Centre_Number,
			@Error = @LocalError

		EXEC @LocalError = AGG_WEEK_LOADER2 @Cost_Centre_Number, @Target_date


		if @LocalError = 0
			BEGIN
				SELECT @Step_End = getdate()

				EXECUTE DW_WRITE_LOG	
				@Process = @LocalProcessName,
				@Package = @LocalPackageName,
				@Message = 'Aggregated WEEK OK',
				@Start = @Step_Start,
				@end = @Step_End,
				@VERSION = @LocalVersion, 
				@Keep = N,
				@Cost_Centre_Number = @Cost_Centre_Number,
				@Error = @LocalError

				UPDATE WEEKLY_AGGREGATION_PROCESS_QUEUE2
				SET Aggregation_Completed_Date  = convert( char(10), getdate(), 112)
				where CURRENT OF cu_WEEKLY_Aggregation_Queue
			END
			 
		ELSE
			BEGIN

				SELECT @Step_End = getdate()

				EXECUTE DW_WRITE_LOG	
				@Process = @LocalProcessName,
				@Package = @LocalPackageName,
				@Message = 'Aggregation ERROR',
				@Start = @Step_Start,
				@end = @Step_End,
				@VERSION = @LocalVersion, 
				@Keep = Y,
				@Cost_Centre_Number = @Cost_Centre_Number,
				@Error = @LocalError
			END


		select @Stopped_Flag = STOP from [ETL_SERVER].ETlControl.dbo.TDL_TASKS where PROCESSNAME = 'AGGREGATION_Q_PROCESSOR'
		fetch next from cu_WEEKLY_Aggregation_Queue INTO @Cost_Centre_Number, @Target_Date, @Aggregation_Completed_Date

	END


close cu_WEEKLY_Aggregation_Queue
deallocate cu_WEEKLY_Aggregation_Queue

quote:

---

Originally posted by AndrewMurphy

You "may" even be able to improve things far more, by eliminiating cursors.....the performance in general is VERY poor. Search here for previous discussions. There are few activities that CANNOT be done in a far more efficient manner...using set-based processing.

---

quote:

---

Originally posted by spirit1

if you have to execute different sprocs for each row of data and those sprocs can't be replaced with an UDF then cursors are the way to go.

Go with the flow & have fun! Else fight the flow

---