Compare records and update values in Stored Procedure

[rct2323]

I am trying to write a stored procedure which will match the records from 2 tables and update a column in each saying the records match by using a counter. Both tables are exactly the same. For instance, if a record in table 1 matches a record in table by using a column as a key, then update the Match column in each table to say "1" then incremement the counter. What makes this difficult is that the records have to be matched by the closest posting date so when records are compared there can be a 1 to many table or many to 1. I believe I got all the criteria squared away. Need help with the following.
1. Performing this for all the records in a table.
2. Assign the select statement into a variable to perform an if statement.

Code is below and any help is greatly appreciated!

create procedure TEST
AS

BEGIN

declare @recordcount as integer
declare @runtime as integer
declare @counter as integer
declare @tempdate as datetime
set @recordcount = 50083
set @runtime = 50083
set @counter = 0
set @tempdate = CURRENT_TIMESTAMP


--while @runtime >= 1
-- INPUT SELECT STATEMENT TO GO THROUGH ALL RECORDS


--begin


If (select *

from reversals,invoices

where reversals.RefDoc_DocNum_BC = invoices.RefDoc_DocNum_BC and
reversals.Billing_Document like '098%' and
invoices.Billing_Document like '095%' and dbo.Reversals.Posting_Date_in_the_Document = dbo.Invoices.Posting_Date_in_the_Document)

/* FIND A WAY TO ASSIGN A VARIABLE FROM A SELECT STATEMENT */



--If (dbo.Reversals.Posting_Date_in_the_Document = dbo.Invoices.Posting_Date_in_the_Document)
begin
update reversals set reversals.Match_BC = @counter
update invoices set invoices.Match_BC = @counter
end

--else
--begin
--For 1 to @recordcount
else
begin
set @tempdate = invoices.Posting_Date_in_the_Document

While @recordcount >= 1
begin
if reversals.Posting_Date_in_the_Document <> @tempdate
begin
set @tempdate = DateAdd(dd,-1,@tempdate)
set @recordcount = @recordcount - 1
end
else
begin
update reversals set reversals.Match_BC = @counter
update invoices set invoices.Match_BC = @counter
end
end
end

--@recordcount = @recordcount - 1
--end



end

 

[bborissov]

That is not right:

update reversals set reversals.Match_BC = @counter
update invoices set invoices.Match_BC = @counter

and it is not right because you will have the last value of @counter stored in ALL records in both tables. You have no WHERE in that UPDATE so ALL records are updated.

I am not sure what you try to do.
Could you post some example data and what you want as a final result?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.