Don’t DROP Temp Tables in SQL Stored Procs

System.Data.SqlClient.SqlException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

I traced the error back to the use of DROP TABLE #tempTableName statements, particularly inside CATCH blocks. Removing the DROP TABLE statements from the CATCH blocks surfaced the real, underlying SQL error messages.

“A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished.” (Reference)

“Dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS. None of these things prevent temporary table caching (so it does not matter whether you explicitly drop a temporary table at the end of a procedure or not).” (Reference)