--this is just creating your temp table and adding some data
-------------------------------------------------------------------
create table #t (Policy_Number varchar(10),Removal_date datetime)

insert into #t values ('P000000034','17/09/07')
insert into #t values ('P000000083','04/09/07')
insert into #t values ('P000000092','19/09/07')
insert into #t values ('P000000114','02/10/07')
-----------------------------------------------------------------------


--this declares some variables that you will use later 
----------------------------------------------------
Declare @PolicyNumber varchar(10), @RemovalDate datetime, @lastremovaldate varchar(20), @maxcallref varchar(20), @last_event_status varchar(10), @ClientAlert4 varchar(200), @ptrval binary(16), @lenca4 int
----------------------------------------------------


--this bit declare a cursor by selecting some records
--it will then go through each of the rows one by one
-------------------------------------------------
declare RemoveRecs cursor for select Policy_Number,Removal_date from #t 

open RemoveRecs
-------------------------------------------------


--this gets the next row (over and over until there are no more)
--you can then 'do something' each each row (inside your begin and end)
------------------------------------
Fetch next from RemoveRecs into @PolicyNumber,@RemovalDate

while (@@fetch_status=0)
begin 
set @lastremovaldate = null


select @lastremovaldate=CLM_Data3 from Clients_more where CLM_Link_To_Client=@PolicyNumber



-----------------------------------------------------------
--this only executes if CLM_Data3 is null for this row
if (@lastremovaldate is NULL) 
	begin
-- set removal_date
	--executes if CLM_data3 is null and CLM_Link_toCLient is null
	if (select CLM_Link_to_Client from clients_more where CLM_Link_to_Client=@PolicyNumber) is null
		begin --prints an error
		print 'Error: Client ' + @PolicyNumber + ' Not Found'
		end
	else
		begin --executes if CLM_data3 is null but CLM_Link_to_Client is not null
		Update clients_more set CLM_Data3 = convert(varchar(20),@RemovalDate,103) where CLM_Link_to_Client = @PolicyNumber
		--adds in the date and prints a messaeg
		print 'Client ' + @PolicyNumber + ' cancelled wih a removal date of '+ convert(varchar(20),@RemovalDate,103)

-- set client_alert_4
		--allocates data to some of the variables to be used later
		SELECT @ptrval = TEXTPTR(Client_Alert_4),@lenca4 = DATALENGTH(Client_Alert_4) FROM Clients WHERE client_ref=@PolicyNumber

		set @ClientAlert4=''
	
		if @lenca4 <> 0 
			begin  --this executes if CLM_data3 is null, CLM_Link_to_Client is not null and the length of Client_Alert_4 is not 0
			set @ClientAlert4=char(13)+char(10) --allocating a carriage rturn and line feed to the variable
			end
		else 
			begin --this executes if CLM_data3 is null, CLM_Link_to_Client is not null and the length of Client_Alert_4 is 0
			Update Clients set client_Alert_4=null WHERE client_ref=@PolicyNumber --sets client_alert_4 to null
			--allocates data to some of the variables for use later
			SELECT @ptrval = TEXTPTR(Client_Alert_4),@lenca4 = DATALENGTH(Client_Alert_4) FROM Clients WHERE client_ref=@PolicyNumber
			end
		--allocates data to the variables for use in the next step
		set @ClientAlert4 = @ClientAlert4 + '********* Customer has Cancelled Policy as of ' + convert(varchar(20),@RemovalDate,103) + '*********'

		--this adds data to your text field. the content of which will vary depend on the route you've taken above
		--i.e. the variables will be different depending on the if's etc..
		Updatetext Clients.Client_Alert_4 @ptrval NULL 0 @ClientAlert4

		--this checks for an error and if it finds one, it prints this message
		if (@@ERROR) <> 0 print 'Error: Failed to update Client Alert for client ' + @PolicyNumber

-- check for open calls

		select @MaxCallRef=max(call_ref) from calls where Link_to_Client = @PolicyNumber
		--this is checking the max (presumably most recent) call_ref that that row / policy number
		If (@MaxCallRef is not NULL)
		begin --this executes only if there is a call_ref i.e. is not null
		select @last_event_status=last_event_status from calls where call_ref=@MaxCallRef
		--this executes only if there is a call_ref, and it is not in the list below (presumably closed?)
		if @last_event_status not in ('RD','X','C')
			begin --prints a warning giving the call_ref
			print 'Warning: Client ' + @PolicyNumber + ' has an open call (' +@MaxCallRef+') ' + 'Status='+@last_event_status
			end
		end
	end 
end --these close off the begins earlier

-----------------------------------------------------------------
else --this will execute if you already have a date in CLM_data3 i.e. is not null
begin
-- Update clients_more set CLM_Data3 = null where CLM_Link_to_Client = @PolicyNumber
print 'Warning: Client ' + @PolicyNumber + ' already has removal date of ' + @lastremovaldate + ', New removal date of '+convert(varchar(20),@RemovalDate,103) + ' not set.'
end

------------------------------------------------------------------

--this fetches the next row to start the loop again
fetch next from RemoveRecs into @PolicyNumber,@RemovalDate
end

--once there are no more rows to loop through it gets rid of the cursor
close RemoveRecs
deallocate RemoveRecs

--and drops the temp table
drop table #t