How should I temporarily store rows in a stored procedure?

Question

In essence I'd like to store some rows in a temporary variable for the life of a procedure in MySQL.

My procedure will grab a column of foreign keys at the beginning of the procedure. Once I'm done working with them I want to update the table to indicate that they have been processed. There may be inserts into this table while my procedure is working on it's set of data so I don't want to erroneously mark these new rows as processed. I also do not want to lock this table and hold up the thread that is making the inserts.

Is a temporary table the best solution?

Answer 1

If it's in SQL Server, investigate using a table variable instead...

Declare @MyFKs  Table 
   (FkId Integer Primary Key Not Null,
    isProcessed bit)

Although they are not as flexible (Can't add multiple indices to them), Table variables do not participate in transaction logging like a temp table does, so, for narrow tables, where you are just storing a key and at most a few data columns, they can be better perfomance than a temp table.

Answer 2

Temporary table will be the easiest and probably the best way to go for this problem.