CODEWITHSUNDEEP
sqlsql-serverloopsstored-procedurescommand
Cara Marie
Cara Marie

Reputation: 1

Stored procedures and the use of temporary tables within them?

I know basic sql commands, and this is my first time working with stored procedures. In the stored procedure I am looking at, there are several temporary tables. The procedure, is triggerred every morning, which then pulls a specific ID and then loops through each ID to grab certain parameters.

My question is: are temporary tables used in stored procedures so that when the procedure goes off the variables will be instantly passed into the parameters and loop, and then the temporary tables will be cleared, thus restarting the process for the next loop?

Upvotes: 0

Views: 1418

Answers (3)

KtX2SkD
KtX2SkD

Reputation: 752

For stored procedures (SPs), they are the least restricted and most capable objects, for example:

  • Their usual alternatives, views and functions, are not allowed to utilize many things, like DML statements, temp. tables, transactions, etc.
  • SPs can avoid returning result sets. They also can return more than one result set.

For temp. tables:

  • Yes, once the SP is done, the table disappears along with its contents (at least for single-# tables).
  • They have advantages & disadvantages compared to their alternatives (actual tables, table varaibles, various non-table solutions).

Upvotes: 2

benjamin moskovits
benjamin moskovits

Reputation: 5458

Temporary tables are used because as soon as the session that created them (or stored procedure) is closed the temporary table is gone. A Temp table with a single # in front of the name (also called a local temp table) is only visible in the session it was created in so a temp table with the same name can be created in multiple sessions without bumping into each other (SQL Server adds characters to the name to make it unique). If a temp table with two ## in front of it is created (a global temp table) then it is unique within SQL Server so other sessions can see it. Temp tables are the equivalent of a scratch pad. When SQL Server is restarted all temp tables and their values are gone. Temp tables can have indexes created against them and SQL Server can use statistics on Temp tables to create efficient query plans.

Upvotes: 2

Alfonso Angeles
Alfonso Angeles

Reputation: 11

Stored Procedures, in my opinion, don't forcibly need Temporary tables. It's up to the SP's scope to decide if using a TempTable is the best approach.

For example, let's suppose that we want to retrieve a List of elements that come out from joining a few tables, then it's the best to have a TempTable to put the joined fields. On the other hand, if we're using a Stored Procedure to retrieve a single or field, I don't see the need for a Temp table.

Temp tables are only available during the usage of the Stored Procedure, once it's finished, the table goes out of scope.

Upvotes: 1

Related Questions