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.

questions:

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' 

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'