MSG 4145 Error

[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

[ck9663]

I did not look at your code...It's quite long. Next time, please use the code tag.

To answer your question, you can either:

1. Issue two updates command for the two tables.

or

2.Create an updateable view and use a trigger to cascade the update.

Good luck!

-- CK

[rct2323]

Hi,
Sorry about that, I'm a newbie. If you forget about my code, how would you write the SQL to do the following:

If you have 1 table with all the data. You want to match rows based on some keys to get a grouping of them and then within the group to identify the 2 rows that should be matched. The 2 items are invoices and reversals. The invoice data should always match to the closest reversal date that is at least equal or after the date. Example below.

ID
123 invoice 1/20/2006 1
123 reversal 1/20/2006 1
123 invoice 1/23/2006 2
123 reversal 2/20/2006 2
123 reversal 1/22/2006

The first record would match with 2nd record so both are labeled with a '1'. 3rd and fourth are matched so are labeled with a '2'. Last record is not matched to anything because the reversal data is before the invoice date. An invoice does not need to have a reversal but if it does it can only have 1. Same thing for a versal.

THANK YOU