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())

   -- First temp table is a materialisation of the XML in the ring buffer.
   -- This is needed, so that following steps performs decently.
   CREATE TABLE #ring_buffer (data xml NULL)

   INSERT #ring_buffer (data)
      SELECT data = CAST(st.target_data AS XML)
      FROM   sys.dm_xe_database_session_targets AS st
      JOIN   sys.dm_xe_database_sessions AS s ON s.address = st.event_session_address
      WHERE  s.name = N'Exceptions'
        AND  st.target_name = N'ring_buffer'

   -- Next is a temp table to hold the result of the initial extract from the events.
   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 events AS(
      SELECT D.d.query('.') AS xml,
             D.d.value('@timestamp', 'datetime2(3)') as timestamp_utc
      FROM   #ring_buffer
      CROSS  APPLY data.nodes('/RingBufferTarget/event') AS D(d)
   ), events2 AS (
      SELECT xml, 
             row_number() OVER(ORDER BY timestamp_utc) AS eventno,
             dateadd(HOUR, 
                datediff(HOUR, getutcdate(), getdate()), timestamp_utc) AS when_ 
      FROM   events
   )
   INSERT #extracted(eventno, when_, name, value, sql_handle, linenum, 
                     stmt_start, stmt_end, tsql_frames)
      SELECT e2.eventno, e2.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   events2 e2
      CROSS  APPLY e2.xml.nodes('/event/*') AS D(d)
      OUTER  APPLY D.d.nodes('value/frames/frame[@level="1"]') AS F(f)
      WHERE  e2.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(object_schema_name(est.objectid), '.', 
                              object_name(est.objectid), '/', 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 'username' 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_stack' THEN sql_handle END) AS sql_handle,
             MIN(CASE name WHEN 'tsql_stack' THEN linenum END) AS linenum,
             MIN(CASE name WHEN 'tsql_stack' THEN stmt_start / 2 END) AS stmt_start,
             MIN(CASE name WHEN 'tsql_stack' 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) + '.' + 
                    object_name(est.objectid) 
          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