How to decide which one to use table variables or any temporary table or a permanent table in a procedure?

The table decision is purely dependent on the use case of the table.

I.e. when the table is required for short span to use within the procedure with small number of column and less 100 number of rows it is prescribed to use a table variable as the table variable is not included in the query plan and work very fast as compared to any other table, but no index be created on this table hence this need to be used for small purposes only.

In case the requirement is to use a table with more than 100 rows and less than 10,000 rows then we should use a local temporary table. In case if the data is larger than 10,000 rows then it will be slower as the space used for temporary tables is in temp DB so it may cause slowness if a lot of tables are created simultaneously. In case data is bigger than 1000 in that case we can create an index on the table which will help or optimize the performance of the procedure. And the scope of the table will remain within the procedure.

In case if the requirement is more than 10,000 it is suggested to use a permanent table as it will have a better execution plan for large tables. And also it will use the DB memory not use TempDB space.