jojacafe
8/29/2016 - 11:00 AM

For each update table

For each update table

	DECLARE @RowsToProcess  int
	DECLARE @CurrentRow     int
	DECLARE @DatetimeNow datetime = GETDATE()

	DECLARE @table TABLE (Id int, ClickAllowed int)  
	INSERT into @table (Id, ClickAllowed) SELECT Id, ClickAllowed FROM Agencies

	SET @RowsToProcess = @@ROWCOUNT
	SET @CurrentRow = 0

	WHILE @CurrentRow < @RowsToProcess
	BEGIN
		SET @CurrentRow = @CurrentRow + 1

		DECLARE @Id int;
		DECLARE @ClickAllowed int;
		DECLARE @StatusID int;
		
		SELECT @Id = Id, @ClickAllowed = ClickAllowed FROM (
			SELECT Id, ClickAllowed,
			ROW_NUMBER() OVER (ORDER BY Id ASC) AS rownumber
			FROM @table
		) as tAgencies
		WHERE tAgencies.rownumber = @CurrentRow

		IF @ClickAllowed > 0
			SET @StatusID = 1;
		ELSE
			SET @StatusID = 3;

		UPDATE Agencies
		SET ClickDaily = @ClickAllowed, StatusId = @StatusID, StatusChangeDate = @DatetimeNow
		WHERE Id = @Id

		--print('Id: ' + CAST(@Id AS VARCHAR) + ' | Clicks: ' + CAST(@ClickAllowed AS VARCHAR))
	END