Temporary tables in procedure parameters

When we declare multiple instances of a record variable referencing the same table in AL code, we essentially receive different instances of the active record, but the same underlying database table behind the variable facade. The values of the variables at any moment may be different, but a Get with the same primary key is guaranteed to return the same value for any of them as long as the database record remains unchanged.

The record behaviour is also quite clear when we pass variables around as arguments to procedures. But is it so obvious when the record variable is declared as temporary?

When we declare two temporary record variables in different scopes and try to retrieve the same record, do our requests hit the same structure in the backend or two separate instances? And what happens when a temporary record is passed to a procedure? Does it still follow the same logic as its non-temporary counterpart?

The correct answer is - as always - it depends. And in fact, quite often it causes a lot of confusion. So let's have a look at a few code samples to see how it actually works and what it depends on.

1. Two temporary record in separate scopes

For the starter, I am declaring two temporary record variables in two separate procedures: one inserts a record into a temporary table, and the other one attempts to retrieve this value.

Here goes the inserting procedure.

local procedure InsertTempRecord()
var
    Customer: Record Customer temporary;
begin
    Customer."No." := 'TEST';
    Customer.Insert();
end;

Here is its reading partner.

local procedure GetTempRecord()
var
    Customer: Record Customer temporary;
begin
    Message(Format(Customer.Get('TEST')));
end;

And a third procedure to link the previous two together.

local procedure InsertAndGet()
begin
    InsertTempRecord();
    GetTempRecord();
end;

Now I run this code and get the answer to the first question: do all instances of the temporary table share the same buffer?

The answer is an unequivocal No, and we can assume that the pretty common approach of calling DeleteAll on a every instance of a temporary table before the first use does not do any good: each instance maintains its own data buffer, and the freshly instantiated temporary table is empty.

2. Temporary record as a var parameter

For the second test, let it be the Item table initialized with two fruits: APPLE and BANANA. And the record variable is now a parameter of both procedures - InsertTempRecord and GetTempRecord - passed by reference.

local procedure InsertTempRecord(var Item: Record Item)
begin
    Item."No." := 'APPLE';
    Item.Insert();

    Item."No." := 'BANANA';
    Item.Insert();
end;

GetTempRecord does not display a message , but just picks the apple from the temporary table (and fails if the apple is not there).

local procedure GetTempRecord(var Item: Record Item)
begin
    Item.Get('APPLE');
end;

The record instance is declared in the InsertAndGet procedure that calls the other two, passing the instance first to InsertTempRecod, then to ReadTempRecord.

local procedure InsertAndGet()
var
    Item: Record Item temporary;
begin
    InsertTempRecord(Item);
    Message('1: ' + Item."No.");

    GetTempRecord(Item);
    Message('2: ' + Item."No.");
end;

The messages I see after running the code is not surprising. It means that both Insert and Get receive a reference to the record variable and modify the same instance that is created in InsertAndGet.

So the active record in the InsertAndGet procedure changes when a callee procedure modifies the record state, and so does the temporary table's data buffer.

3. Record parameter without var

Now I repeat the same actions I did in the previous example with one change - remove the "var" modifier in procedure parameters, so that the parameter declaration looks like this:

local procedure InsertTempRecord(Item: Record Item)

I do it for both InsertTempRecord and GetTempRecord and execute the code again. It runs successfully: insert still inserts both fruits, and Get still gets its share. Both messages appear blank, except the numbers: no apple or banana in the caller procedure. We can accept that, since the record is passed by value and the state of the active record in InsertAndGet is not affected by other procedures' actions. The result looks OK at first sight, until I look into the database and find both fruits sitting there next to desks and chairs from the demo dataset.

Moral: be double cautious when when sending a temporary record to a procedure and make sure you've included the var keyword in the declaration if you intend to update the table data. Without it the queries are sent to the database table instead of the temporary instance.

3.1 A bonus test

As a bonus test, try calling the following procedure sending it a temporary Item record.

local procedure CheckIsTemporary(var Item: Record Item)
begin
    Message(Format(Item.IsTemporary()));
end;

The message will say an unambiguous "Yes": it is obviously a temporary record. But remove "var" in the parameter declaration and and you will get a "No" instead. We are not dealing with the temporary table anymore if it is passed to a procedure by value.

4. Record parameter with the "temporary" property

And in the final round, I add the temporary property to the procedure parameter itself in two versions: with or without the var keyword.

Long story short (and this is the reason I combined two scenarios in one section): the "var" version with the temporary parameter property is no different from the test above where the record variable was passed by reference, but without marking the procedure parameter as temporary. Insert and Get are executed on the same copy of the variable and access data in its underlying buffer.

The code sample behaves differently when the parameters are declared as temporary, but without var, passed by value.

local procedure InsertTempRecord(Item: Record Item temporary)

When I run the InsertAndGet procedure now, I receive a different message:

Looks like I'm out of apples here.

And this message clearly tells us that the three temporary record variables - one in each procedure - work with different in-memory data buffers.

As a conclusion, a few bullet points drawn from all this text:

• Each temporary table is backed by its own data buffer (no need to reset and delete anything from it straight after the declaration).

• Temporary record passed to a procedure by reference remains temporary - it is the same instance we deal with in the caller and the callee.

• Temporary record passed to a procedure by value is not temporary anymore: it actually points to the real database table. Mind the gap!

• Adding the temporary property to the parameter dos not change anything for a by-ref parameter, but in case of a by-value variable, sets the data provider of the argument to another buffer, separate from the caller's value.