Question

This is a SQL question.

Which construct(s) can be created inside of a stored procedure? (Select all that apply)

A. Global temp tables

B. Local temp tables

C. Table variables

D. CTEs

Answer 1

All of the options can be created inside of a stored procedure only the scope of the constructs vary.

A. Global temp tables

Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.

B. Local temp tables

Local temp tables work same as local temp tables just that the scope is local to the stored procedure

C. Table variables

Table variables can be declared within batches, functions, and stored procedures, and table variables automatically go out of scope when the declaration batch, function, or stored procedure goes out of scope.

D. CTEs

If we create the CTE in a Stored Procedure, it can’t use it in another Stored Procedure.