Problem referencing a column in a Table Variable in a query.

Hello All

I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @.THeader" . @.THeader is the name of the variable table and the error occurs with such references as @.THeader.ApplyAmt, @.THeader.TransactionHeaderID, etc.

declare @.THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable

insert into @.THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable

UPDATE @.THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @.THeader, mtblTransactionAllocation - do the updates on the table variable

Update @.THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @.THeader, mtblTransactionAllocation - do the updates on the table variable

- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @.THeader.ApplyAmt

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @.THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @.THeader.ApplyAmt
FROM @.THeader, mtblTransactionHeader
WHERE @.THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table

Thanks in advance

smHaig

Try adding an alias to @.THeader

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = t.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = t.ApplyAmt
FROM @.THeader t, mtblTransactionHeader
WHERE t.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID -- put the values back into original table