Menu
In a stored procedure if I
DECLARE @tmpClientTable TABLE
Would I need to drop it again? Or would it just get overwritten next time stored proc runs.
What would happen if someone else ran the stored proc too....would that be a separate table?
920
Insert data into a table variable. We do not require dropping the table variable. As mentioned earlier, the scope of the table variable is within the batch. The scope of it lasts at the end of the batch or procedure.
Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.
It's ok to explicitly include DROP TABLE or let SQL Server handle it for you each time the connection drops. If your stored procedures are already dropping temp tables, that's great. If they're not being dropped, I wouldn't bother spending the time and effort just to drop them.
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.
You don't need to (and actually can't) explicitly drop it.
The creation and dropping of table variables is handled for you automatically.
In a stored procedure the table variables can be cached rather than repeatedly being dropped and created. See Temporary Table Caching Explained for more about this.
A concurrent user would get a separate execution context and separate instance of the table variable.
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
