How to handle job failure in SQL Server Management Studio?

I have created a scheduled job in SQL Server Management Studio which executes a stored procedure at a specific time. However, some of the time it fails to execute the procedure due to deadlock, as seen in Job history. Now I want to insert a value in a table which includes job failure detail whenever the job fails to run completely. How can I achieve this? Thanks!

I'm assuming that you are looking to capture what went wrong within your stored procedure and keep that information (because "whenever the job fails to run completely" includes other possibilities that this solution does not cover, for example if the job fails to start due to login failure, or if that account can't access your stored procedure).

If you surround the current contents of your stored procedure with TRY...CATCH like this:

BEGIN TRY
    -- the current contents of your stored procedure
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Then you can INSERT that information into a table, along with the time when it happened.

For more information see Microsoft's Transact SQL documentation for TRY...CATCH, which includes a list of things that are not trapped by TRY...CATCH.