Trying to determine the status of a job in TSQL can be difficult if you are trying to do it through automated code.  You can't use a tempory table to store the results of sp_help_job since it already does this, and nested inserts of executing procedures is not allowed.  To get around this limitation, you can use a CLR TVF to return the output of sp_help_job as a table.

Monitoring ErrorLogs in SQL Server can be done a number of ways, but if you want to scan the contents in SQL and use filters to SELECT specific events from the Error Log in TSQL, it has traditionally required creating a temporary table to hold the results of xp_readerrorlog and then running your selects against this temp table.  This method requires a lot of code, and isn't a fast way to pull data on the fly.  Now with SQL CLR and Table Valued Functions it is simple to create a permanent solution to this problem.

One of the requirements of a project we implemented was to parse all the hyperlinks from documents and insert them into a table.  This clr stored procedure uses regex to parse all hyperlinks and insert each into into a table and column specified in the procedure.  Since the procedure accesses the file system it is necessary to set the permission to External.  There is extra code that I used to debug sending the statements to the message tab.  There are comments in these areas, but I left them in to make it easier to become familiar with the code and change as needed.

This clr procedure accepts a file path and a FOR XML query and saves the xml results to the specified file.

The following example uses the clr to write to the event log.