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_TIMESTA MP


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


--begin


If (select *

from reversals,invoi ces

where reversals.RefDo c_DocNum_BC = invoices.RefDoc _DocNum_BC and
reversals.Billi ng_Document like '098%' and
invoices.Billin g_Document like '095%' and dbo.Reversals.P osting_Date_in_ the_Document = dbo.Invoices.Po sting_Date_in_t he_Document)

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



--If (dbo.Reversals. Posting_Date_in _the_Document = dbo.Invoices.Po sting_Date_in_t he_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.Postin g_Date_in_the_D ocument

While @recordcount >= 1
begin
if reversals.Posti ng_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]

follow up

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