USE Northwind
go
CREATE OR ALTER PROCEDURE dbo.list_exceptions_sp @since datetime2(3) = NULL AS 
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
   -- Default for @since is 24 hours.
   IF @since IS NULL
      SELECT @since = dateadd(HOUR, -24, sysdatetime())

   -- We need a temp table to hold the result of the initial extract.
   CREATE TABLE #extracted (eventno       int           NOT NULL,
                            when_         datetime2(3)  NOT NULL,
                            name          nvarchar(128) NOT NULL,
                            value         nvarchar(MAX) NULL,
                            sql_handle    varbinary(85) NULL,
                            linenum       int           NULL,
                            stmt_start    int           NULL,
                            stmt_end      int           NULL,
                            tsql_frames   xml           NULL,
                            PRIMARY KEY (eventno, name)
    )

   ; WITH CTE AS (
      SELECT cast(event_data AS xml) AS xml,
             row_number() OVER(ORDER BY timestamp_utc, file_offset) AS eventno,
             dateadd(HOUR, 
                 datediff(HOUR, getutcdate(), getdate()), timestamp_utc) AS when_
      FROM   sys.fn_xe_file_target_read_file ('Exceptions*.xel', DEFAULT, NULL, NULL)
   )
   INSERT #extracted(eventno, when_, name, value, sql_handle, linenum, 
                     stmt_start, stmt_end, tsql_frames)
      SELECT CTE.eventno, CTE.when_, 
             D.d.value('@name', 'nvarchar(128)') AS name, 
             D.d.value('(value/text())[1]', 'nvarchar(MAX)') AS value,
             convert(varbinary(85),
                 F.f.value('@handle', 'varchar(200)'), 1) AS sql_handle,
             F.f.value('@line', 'int') AS linenum,
             F.f.value('@offsetStart', 'int') AS stmt_start,
             F.f.value('@offsetEnd', 'int') AS stmt_end,
             D.d.query('value/frames') AS tsql_frames
      FROM   CTE
      CROSS  APPLY CTE.xml.nodes('/event/*') AS D(d)
      OUTER  APPLY D.d.nodes('value/frame') AS F(f)
      WHERE  CTE.when_ >= @since

   -- And this is the final SELECT with three CTEs for the call stack, and one
   -- to pivot the data in the temp table.
   ; WITH call_frames AS (
      SELECT eventno, F.f.value('@level', 'int') AS level,
                      F.f.value('@line', 'int') AS line,
                      convert(varbinary(85), 
                             F.f.value('@handle', 'varchar(200)'), 1) AS sql_handle
      FROM   #extracted 
      CROSS  APPLY tsql_frames.nodes('frames/frame') F(f)
   ), callstrings AS (
      SELECT c.eventno, c.level, 
             CASE WHEN est.dbid = 32767 THEN object_name(est.objectid)
                  WHEN est.objectid IS NOT NULL
                  THEN concat(db_name(est.dbid), '.', 
                              object_schema_name(est.objectid, est.dbid), '.', 
                              object_name(est.objectid, est.dbid), '/', c.line)
                  WHEN est.text IS NOT NULL THEN concat('<Ad-hoc>/', c.line)
                  ELSE 'NULL'
             END AS callstring
      FROM   call_frames c
      OUTER  APPLY  sys.dm_exec_sql_text(c.sql_handle) est
   ), callstringagg AS (
      SELECT  eventno, 
              string_agg(callstring, ' - ') WITHIN GROUP (ORDER BY level DESC) AS callstack,
              SUM(CASE WHEN callstring <> 'NULL' THEN 1 ELSE 0 END) AS nonullcnt
      FROM    callstrings
      GROUP   BY eventno
   ), pivoted AS (
      SELECT eventno, when_,
             MIN(CASE name WHEN 'client_app_name' THEN value END) AS appname,
             MIN(CASE name WHEN 'client_hostname' THEN value END) AS hostname,
             MIN(CASE name WHEN 'session_server_principal_name' THEN value END) AS username,
             MIN(CASE name WHEN 'error_number' THEN CAST(value AS int) END) AS errno,
             MIN(CASE name WHEN 'message' THEN value END) AS errmsg,
             MIN(CASE name WHEN 'database_name' THEN value END) AS DB,
             MIN(CASE name WHEN 'sql_text' THEN value END) AS batch_text,
             MIN(CASE name WHEN 'tsql_frame' THEN sql_handle END) AS sql_handle,
             MIN(CASE name WHEN 'tsql_frame' THEN linenum END) AS linenum,
             MIN(CASE name WHEN 'tsql_frame' THEN stmt_start / 2 END) AS stmt_start,
             MIN(CASE name WHEN 'tsql_frame' THEN IIF(stmt_end = -1, 1000000000, 
                  (stmt_end - stmt_start) / 2 + 1) END) AS stmt_len
      FROM   #extracted 
      GROUP BY eventno, when_
   )
   SELECT p.when_, p.username, p.hostname, p.appname, p.errno, p.errmsg, p.DB, 
          CASE WHEN est.dbid = 32767 THEN object_name(est.objectid)
               ELSE object_schema_name(est.objectid, est.dbid) + '.' + 
                    object_name(est.objectid, est.dbid) 
          END AS SPname,
          p.linenum,  p.batch_text, substring(est.text, stmt_start + 1, stmt_len) AS statement,
          CASE WHEN c.nonullcnt > 0 THEN c.callstack END AS callstack
   FROM   pivoted p
   LEFT   JOIN callstringagg c ON c.eventno = p.eventno
   OUTER  APPLY sys.dm_exec_sql_text(p.sql_handle) est
   ORDER  BY when_ DESC
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH
GO