drop table in a begin try begin catch block - is there any loss of performance?

Question

The code below is a dynamic sql that will give you a list of all alerts in your system.

It works.

There is a temp table in the code for reasons beyond this question.

This question is related to the way I make sure the temp table does not exist, before I create it.

I put a drop table in a try catch block as you can see in the code below.

My questions are:

• Is there any loss of performance?
• Any disadvantage in doing this?
• Why is this not considered generally best practices?

I still have some servers sql-server-2008-r2 but mostly my servers are sql server 2016.

EXEC Sp_executesql 
N'  

begin try
 drop table #tmp_sp_help_alert 
end try
begin catch
end catch

create table #tmp_sp_help_alert  (
id int null, name nvarchar(128) null,  
event_source nvarchar(100) null, 
event_category_id int null, 
event_id int null, 
message_id int null,  
severity int null, 
enabled tinyint null, 
delay_between_responses int null, 
last_occurrence_date int null, 
last_occurrence_time int null, 
last_response_date int null, 
last_response_time int null, 
notification_message nvarchar(512) null, 
include_event_description tinyint null, 
database_name nvarchar(128) null, 
event_description_keyword nvarchar(100) null, 
occurrence_count int null, 
count_reset_date int null, 
count_reset_time int null, 
job_id uniqueidentifier null, 
job_name nvarchar(128) null, 
has_notification int null, 
flags int null, 
performance_condition nvarchar(512) null, 
category_name nvarchar(128) null,  
wmi_namespace nvarchar(max) null,  
wmi_query nvarchar(max) null, 
type int null)   

insert into #tmp_sp_help_alert 
exec msdb.dbo.sp_help_alert         



SELECT * FROM #tmp_sp_help_alert AS tsha --WHERE (tsha.name=@_msparam_0)   
--drop   table #tmp_sp_help_alert

', 
    N'@_msparam_0 nvarchar(4000)', 
    @_msparam_0=N'SQLPROD2 Alert - AG Role Change' 

Answer 1

It's better to check for the table existence rather than trying to drop it and ignore any errors. This is generally considered a bad practice in all programming languages. In SQL Server and all RDBMSs you have metadata, so there are certainly better ways of doing this.

An example:

IF OBJECT_ID('tempdb..#tmp_sp_help_alert') IS NOT NULL
    DROP TABLE #tmp_sp_help_alert;

Moreover, temporary tables are automatically deleted when they get out of scope (for local temporary tables, when the procedure ends), so it might be overkill to test for table existence. For the same reason, it might also be unnecessary to drop the table at the end of the scope.