Probably the best way to log whether your stored procedure ran correctly or threw an error during its run is to create another stored procedure to capture the events of the run.
This example although basic captures the primary data you would want to keep a record of regarding a procedures run.
- The name of the stored procedure
- The start time of the stored procedure
- The time the stored procedure finished/errored
- A user comment/error message
- An assigned Log ID
Below is a pre-baked example requiring just a change to the DatabaseName, and if you wish SP_Name, text fields, as instructed in the comments of the SQL below, to test run the script.
This will create a SP_Log table and the stored procedure InsertRunIntoSpLog which can be reused over and over again when making stored procedures for other jobs to log the success/failure of these jobs.
Step 3 is an example stored procedure, which can be renamed as something other than SP_Name, which simply displays the date time. Once created whenever the following is executed, EXEC dbo.SP_Name, the current date time will be returned and a record of the procedures run will be added to a row in the SP_Log table.
Although as stated above this is a basic example it is a very powerful foundation which can be built upon for very comprehensive event and transaction logging.