Forum Discussion

AJ-AJ's avatar
AJ-AJ
Copper Contributor
Jun 28, 2024

Help to Capture SP error without try catch block

Hi there, 
 
I have 100s of SPs to make change at one shot without touching existing logic. 
 
Many SPs have try catch block many dont have.
 
im looking to track if sp ran successful or had failed (if possible why it failed). As you could see im tracking start and end time. Would be helpful if i could track status and errors. add same line of code to all sps without dependency on try catch block.
 
Thanks
 
ALTER PROCedure [Audit].[sptemplate]
as 
begin
--Assign Execution id
DECLARE @UID UNIQUEIDENTIFIER = NEWID()
 
--Capture StartTime
declare @starttime datetime = getdate() 
 
/*
insert into anothertable
select from table
 
business logic
 
*/
 
--Capture StartTime
declare @endtime datetime = getdate()
 
--Write to LogTable
insert into [Audit].[ETLRunTimeLog] ([LogID],[ProcessName],[StartTime],[EndTime])
select @UID u,(SELECT OBJECT_NAME(@@PROCID) ), @starttime s, @endtime e 
end

 

 

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jul 01, 2024

    AJ-AJ , on SQL Server side it's not possible to log error without a TRY/CATCH block.

    If the SP are called from an application, then the app have to log errors.

    If the SP are called from a Agent-job, then add a "On eror" step to log the raised error.

Resources