Hello! In this article I share a community tool I put together to drop all of the #temporary tables for the current SQL Server session. Have feedback or questions about the content? Feel free to reach out to me at wes@switcharch.com or @wesatswitcharch.
It’s no secret that the only T-SQL development pattern I use more than stored procedures is #temporary tables inside of stored procedures (ha!). Paul White said it best in this blog post – smaller result sets often give the optimizer a better chance of producing a good query plan.
The Problem
The drawback to using temporary tables in stored procedures is that when you start taking code out of the stored procedure to debug it you will find yourself crafting the following statement over and over again:
IF OBJECT_ID('[tempdb].[dbo].[#Company]') IS NOT NULL
DROP TABLE [#Company];
IF OBJECT_ID('[tempdb].[dbo].[#Product]') IS NOT NULL
DROP TABLE [#Product];
IF OBJECT_ID('[tempdb].[dbo].[#ResultSet]') IS NOT NULL
DROP TABLE [#ResultSet];
Building a query to drop your temporary tables isn’t as easy as you would think. Why?